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 ) tempEOT;$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.