CodeIgniter4 DataTables Usage - Join Query

Click to Load Demo

Table Employee

First name	Last Name	Email	Office code	Office city

Note

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

Controller

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

                    
use \Hermawan\DataTables\DataTable;

public function join_query()
{
    $db = db_connect();
    $builder = $db->table('employees')
                  ->select('firstName, lastName, email, offices.officeCode, offices.city')
                  ->join('offices', 'offices.officeCode = employees.officeCode');

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

Javascript

                    
$(document).ready(function() {
    $('#table').DataTable({
        processing: true,
        serverSide: true,
        ajax: '/ajax-datatable/join-query',
        columns: [
            {data: 'firstName'},
            {data: 'lastName'},
            {data: 'email'},
            {data: 'officeCode'},
            {data: 'city'}
        ]
    });
});

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/join-query',
        columns: [
            {data: 'firstName',  name: 'employees.firstNname'},
            {data: 'lastName',   name: 'employees.lastName'},
            {data: 'email',      name: 'employees.email'},
            {data: 'officeCode', name: 'offices.officeCode'},
            {data: 'city',       name: 'offices.city'}
        ]
    });
});

AJAX Response :

                    
{
    "draw": "1",
    "recordsTotal": 23,
    "recordsFiltered": 23,
    "data": [
        {
            "firstName": "Andy",
            "lastName": "Fixter",
            "email": "afixter@classicmodelcars.com",
            "officeCode": "6",
            "city": "Sydney"
        },
        {
            "firstName": "Anthony",
            "lastName": "Bow",
            "email": "abow@classicmodelcars.com",
            "officeCode": "1",
            "city": "San Francisco"
        },
        {
            "firstName": "Barry",
            "lastName": "Jones",
            "email": "bjones@classicmodelcars.com",
            "officeCode": "7",
            "city": "London"
        },
        {
            "firstName": "Diane",
            "lastName": "Murphy",
            "email": "dmurphy@classicmodelcars.com",
            "officeCode": "1",
            "city": "San Francisco"
        },
        {
            "firstName": "Foon Yue",
            "lastName": "Tseng",
            "email": "ftseng@classicmodelcars.com",
            "officeCode": "3",
            "city": "NYC"
        },
        {
            "firstName": "George",
            "lastName": "Vanauf",
            "email": "gvanauf@classicmodelcars.com",
            "officeCode": "3",
            "city": "NYC"
        },
        {
            "firstName": "Gerard",
            "lastName": "Bondur",
            "email": "gbondur@classicmodelcars.com",
            "officeCode": "4",
            "city": "Paris"
        },
        {
            "firstName": "Gerard",
            "lastName": "Hernandez",
            "email": "ghernande@classicmodelcars.com",
            "officeCode": "4",
            "city": "Paris"
        },
        {
            "firstName": "Jeff",
            "lastName": "Firrelli",
            "email": "jfirrelli@classicmodelcars.com",
            "officeCode": "1",
            "city": "San Francisco"
        },
        {
            "firstName": "Julie",
            "lastName": "Firrelli",
            "email": "jfirrelli@classicmodelcars.com",
            "officeCode": "2",
            "city": "Boston"
        }
    ]
}