Datatables + PHP: Server-Side Processing on Multiple Tables

Question

How can I get Datatables Server-Side Processing script to work with a custom query? I need to select columns from multiple tables and have Datatables render them.

Datatables.net's Server-Side Processing (SSP) with PHP is summarized here: https://datatables.net/examples/server_side/simple.html

I found this SO question, but the original poster never provided his solution. I don't have sufficient reputation to ask him to provide more detail.

Here is my raw SQL without using Datatable's SSP

SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName
FROM tbl_houses, tbl_residents
WHERE tbl_houses.houseID = tbl_residents.residentID

/* 
* # Equivalent query using JOIN suggested by @KumarRakesh
* # Note: JOIN ... ON is a synonym for INNER JOIN ... ON
* # Using JOIN conforms to syntax spec'd by ANSI-92 https://stackoverflow.com/a/894855/946957
*
* SELECT tbl_houses.style, tbl_houses.roomCount, tbl_residents.firstName, tbl_residents.lastName 
* FROM tbl_houses 
* JOIN tbl_residents ON tbl_houses.houseID = tbl_residents.residentID
*/

How can I get Datatables to run queries off the above using SSP?

It appears server_processing.php only accepts 1 table and no custom filtering (i.e., WHERE clauses).

// DB table to use
$table = 'datatables_demo';

// Table's primary key
$primaryKey = 'id';

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 * If you just want to use the basic configuration for DataTables with PHP
 * server-side, there is no need to edit below this line.
 */

require( 'ssp.class.php' );

echo json_encode(
    SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns )
);

However, ssp.class.php does support filtering using WHERE. I'm thinking I need to modify ssp.class.php to force in my WHERE clause

UPDATE

Found a solution. Will post when I have free time.

Answer 1

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.