Datatables + PHP: Server-Side Processing on Multiple Tables
Solution 1
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.
Solution 2
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.
Solution 3
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.
Related videos on Youtube
Christian
Self-taught developer. My main focus is on building financial analysis applications.
Updated on June 04, 2022Comments
-
Christian almost 2 years
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 modifyssp.class.php
to force in myWHERE
clauseUPDATE
Found a solution. Will post when I have free time.
-
Kumar Rakesh over 7 yearsYou can also do it .. with SELECT tbl_house.style, tbl_house.roomCount, tbl_residents.firstName, tbl_residents.lastName FROM tbl_house JOIN tbl_resident ON tbl_house.houseID = tbl_resident.residentID
-
-
Christian over 7 yearsThanks for the deep dive! I implemented your suggestion and you are correct! The datatables
ssp.class.php
does not support multiple tables out-of-the-box. That is,$table = 'tbl_houses, tbl_residents'
produces a Base table or view not found: 1146 error. However, I did find a solution and will post when I have a free minute. -
Milson over 7 yearsHave you tried to modify it for supporting "UNION ALL"?
-
Christian over 7 years@Milson been meaning to post a draft up to Github with a pull-request to Emran. My mods were mainly simplification of syntax used to call ssp functions/procedures and documentation clarity. Adding
UNION
functionality should be trivial. Taking a look at the changes Emran made to turn onJOIN
gives context on how to implement other SQL functions