Datatables + PHP: Server-Side Processing on Multiple Tables

SOLUTION

Class ssp.class.php doesn't support joins and sub-queries, but there is a workaround. The trick is to use sub-query as shown below in $table definition. Replace table with your actual table name in the sub-query.

$table = <<<EOT
 (
    SELECT 
      a.id, 
      a.name, 
      a.father_id, 
      b.name AS father_name
    FROM table a
    LEFT JOIN table b ON a.father_id = b.id
 ) temp
EOT;

$primaryKey = 'id';

$columns = array(
   array( 'db' => 'id',          'dt' => 0 ),
   array( 'db' => 'name',        'dt' => 1 ),
   array( 'db' => 'father_id',   'dt' => 2 ),
   array( 'db' => 'father_name', 'dt' => 3 )
);

$sql_details = array(
   'user' => '',
   'pass' => '',
   'db'   => '',
   'host' => ''
);

require( 'ssp.class.php' );
echo json_encode(
   SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

You also need to edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks.

Make sure all column names are unique otherwise use AS to assign an alias.

NOTES

There is also github.com/emran/ssp repository that contains enhanced ssp.class.php supporting JOINs.

LINKS

See jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php for more information.

TL;DR: I ended up using a modification of the original Datatables ssp.class.php called ssp.php implemented by Emran Ul Hadi: https://github.com/emran/ssp

His modification accepts JOIN, WHERE, GROUP BY and column aliases. Although the file hasn't been updated in over a year, it still works with DataTables 1.12.x. I made some modifications to his version that increases its robustness and improves the documentation with clearer examples.

Will post my mods/updates here when I have a bit more time. Eventually I hope to put in a pull-request to get my updates into his repository.

It looks like the scripts from DataTables are indeed not designed for your particular use case. But there is a method that allows for custom where clauses and from reading the source of ssp.class.php#complex I think that this configuration should work for your by using the WHERE method. The JOIN method will not work here.

Long story short: edit your server_processing.php to that:

<?php
// DB table to use
$table = 'tbl_houses, tbl_residents';

// First table's primary key
$primaryKey = 'tbl_houses.id';

$columns = [
    [ 'db' => 'tbl_houses.style'],
    [ 'db' => 'bl_houses.roomCount'],
    [ 'db' => 'tbl_residents.firstName'],
    [ 'db' => 'tbl_residents.lastName']

);

// connection details
$sql_details = [

];

$whereAll = 'tbl_houses.houseID = tbl_residents.residentID';

require( 'ssp.class.php' );

echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns , null, $whereAll);
);

The complex method accepts your custom WHERE clause. But the tricky thing is the usage of 2 tables. This is what the script does not seem to be designed for. I took a look on how it is building the final sql query and it appears that you may just use this table_name.field_name notation in the config, as well as the table_name, table_name notation for the $table and $primaryKey variables.

As mentioned, the usage of 2 tables is not intended by the DataTables script. I don't know if all features of DataTables will work with that.