CodeIgniter4 DataTables Usage - columns.name option

Click to Load Demo

Table Products

Product Code	Product name	Vendor	Quantity	Price

Note

This sample database is downloaded from : https://www.mysqltutorial.org/mysql-sample-database.aspx/

Controller

                    
use \Hermawan\DataTables\DataTable;

public function column_name()
{
    $db = db_connect();
    $builder = $db->table('products')
                  ->select('productCode')
                  ->select("CONCAT(productLine, ' - ', productName) as productLineName")
                  ->select('productVendor, quantityInStock, buyPrice');
    
    return DataTable::of($builder)->toJson(true);
}

Javascript

When columns.name property has been set, that value will be used for search/order columns.

For example code below. column displayed on table will be use productLineName but when you search/order it will use products.productName column. So, productLine column (from SELECT CONCAT query) will be not searchable or orderable

                    
$(document).ready(function() {
    $('#table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '/ajax-datatable/column-name',
        columns: [
            {data: 'productCode'},
            {data: 'productLineName', name: 'products.productName' },
            {data: 'productVendor'},
            {data: 'quantityInStock'},
            {data: 'buyPrice'}
        ]
    });
});

AJAX Response :

                    
{
    "draw": "1",
    "recordsTotal": 110,
    "recordsFiltered": 110,
    "data": [
        {
            "productCode": "S10_1678",
            "productLineName": "Motorcycles - 1969 Harley Davidson Ultimate Chopper",
            "productVendor": "Min Lin Diecast",
            "quantityInStock": "7933",
            "buyPrice": "48.81"
        },
        {
            "productCode": "S10_1949",
            "productLineName": "Classic Cars - 1952 Alpine Renault 1300",
            "productVendor": "Classic Metal Creations",
            "quantityInStock": "7305",
            "buyPrice": "98.58"
        },
        {
            "productCode": "S10_2016",
            "productLineName": "Motorcycles - 1996 Moto Guzzi 1100i",
            "productVendor": "Highway 66 Mini Classics",
            "quantityInStock": "6625",
            "buyPrice": "68.99"
        },
        {
            "productCode": "S10_4698",
            "productLineName": "Motorcycles - 2003 Harley-Davidson Eagle Drag Bike",
            "productVendor": "Red Start Diecast",
            "quantityInStock": "5582",
            "buyPrice": "91.02"
        },
        {
            "productCode": "S10_4757",
            "productLineName": "Classic Cars - 1972 Alfa Romeo GTA",
            "productVendor": "Motor City Art Classics",
            "quantityInStock": "3252",
            "buyPrice": "85.68"
        },
        {
            "productCode": "S10_4962",
            "productLineName": "Classic Cars - 1962 LanciaA Delta 16V",
            "productVendor": "Second Gear Diecast",
            "quantityInStock": "6791",
            "buyPrice": "103.42"
        },
        {
            "productCode": "S12_1099",
            "productLineName": "Classic Cars - 1968 Ford Mustang",
            "productVendor": "Autoart Studio Design",
            "quantityInStock": "68",
            "buyPrice": "95.34"
        },
        {
            "productCode": "S12_1108",
            "productLineName": "Classic Cars - 2001 Ferrari Enzo",
            "productVendor": "Second Gear Diecast",
            "quantityInStock": "3619",
            "buyPrice": "95.59"
        },
        {
            "productCode": "S12_1666",
            "productLineName": "Trucks and Buses - 1958 Setra Bus",
            "productVendor": "Welly Diecast Productions",
            "quantityInStock": "1579",
            "buyPrice": "77.90"
        },
        {
            "productCode": "S12_2823",
            "productLineName": "Motorcycles - 2002 Suzuki XREO",
            "productVendor": "Unimax Art Galleries",
            "quantityInStock": "9997",
            "buyPrice": "66.27"
        }
    ]
}