CodeIgniter4 DataTables Usage - Subquery

Table Order

Order No.	Order Date	Cust. Name	Count ordered item	Order Status

Note

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

Controller

just do on query builder. It's recommended set toJson() argument to true return data as array of objects will easier to handle.

                    
use \Hermawan\DataTables\DataTable;

public function subquery()
{
    $db = db_connect();
    $builder = $db->table('orders')
                  ->select('orderNumber, orderDate, customers.customerName')
                  ->select('(SELECT count(*) FROM orderdetails where orderdetails.orderNumber = orders.orderNumber) as countItem')
                  ->select('status')
                  ->join('customers', 'customers.customersNumber = orders.customersNumber');

    return DataTable::of($builder)->toJson(true);
}

Javascript

                    
$(document).ready(function() {
    $('#table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '/ajax-datatable/subquery',
        columns: [
            {data: 'orderNumber'},
            {data: 'orderDate'},
            {data: 'customerName'},
            {data: 'countItem'},
            {data: 'status'},
        ]
    });
});

Some case may we meet ambiguous column name. Or maybe want to custom orderable / searchable column Add name property on columns option with table.column_name

                    
$(document).ready(function() {
    $('#table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '/ajax-datatable/subquery',
        columns: [
            {data: 'orderNumber',   name: 'orders.orderNumber'},
            {data: 'orderDate',     name: 'orders.orderDate'},
            {data: 'customerName',  name: 'customers.customerName'},
            {data: 'countItem',     name: 'countItem'},
            {data: 'status',        name: 'orders.status'},
        ]
    });
});

AJAX Response :

                    
{
    "draw": "1",
    "recordsTotal": 326,
    "recordsFiltered": 326,
    "data": [
        {
            "orderNumber": "10100",
            "orderDate": "2003-01-06",
            "customerName": "Online Diecast Creations Co.",
            "countItem": "4",
            "status": "Shipped"
        },
        {
            "orderNumber": "10101",
            "orderDate": "2003-01-09",
            "customerName": "Blauer See Auto, Co.",
            "countItem": "4",
            "status": "Shipped"
        },
        {
            "orderNumber": "10102",
            "orderDate": "2003-01-10",
            "customerName": "Vitachrome Inc.",
            "countItem": "2",
            "status": "Shipped"
        },
        {
            "orderNumber": "10103",
            "orderDate": "2003-01-29",
            "customerName": "Baane Mini Imports",
            "countItem": "16",
            "status": "Shipped"
        },
        {
            "orderNumber": "10104",
            "orderDate": "2003-01-31",
            "customerName": "Euro+ Shopping Channel",
            "countItem": "13",
            "status": "Shipped"
        },
        {
            "orderNumber": "10105",
            "orderDate": "2003-02-11",
            "customerName": "Danish Wholesale Imports",
            "countItem": "15",
            "status": "Shipped"
        },
        {
            "orderNumber": "10106",
            "orderDate": "2003-02-17",
            "customerName": "Rovelli Gifts",
            "countItem": "18",
            "status": "Shipped"
        },
        {
            "orderNumber": "10107",
            "orderDate": "2003-02-24",
            "customerName": "Land of Toys Inc.",
            "countItem": "8",
            "status": "Shipped"
        },
        {
            "orderNumber": "10108",
            "orderDate": "2003-03-03",
            "customerName": "Cruz & Sons Co.",
            "countItem": "16",
            "status": "Shipped"
        },
        {
            "orderNumber": "10109",
            "orderDate": "2003-03-10",
            "customerName": "Motor Mint Distributors Inc.",
            "countItem": "6",
            "status": "Shipped"
        }
    ]
}