jQuery DataTables: Using WHERE, JOIN and GROUP BY with ssp.class.php

July 23, 2017July 27, 2017 Michael Ryvkin

jQuery DataTables distribution comes with server_processing.php script and ssp.class.php class that add support for server-side processing and better performance for datasets with more that 10,000 records. However these files need to be tweaked if your query contains WHERE, JOIN or GROUP BY clauses.

Using `WHERE` clause

Class ssp.class.php comes with two methods – SSP::simple() and SSP::complex().

Usage of SSP::simple() method is demonstrated in the server_processing.php script.

Method SSP::complex() has two additional arguments $whereResult and $whereAll that allow to add WHERE clause to a query.

$whereResult – Condition to apply to result set only
If you need to apply condition to your dataset and let user know that larger dataset is available, supply your condition using $whereResult argument. jQuery DataTables will show something like Showing 1 to X of X entries (filtered from X total entries) in the information panel.
$whereAll – Condition to apply to all queries
If you need to apply condition to your dataset without letting user know that larger dataset is available, supply your condition using $whereAll argument. jQuery DataTables will show something like Showing 1 to X of X entries in the information panel.

Most of the time you need to apply your condition using $whereAll argument and use null as a value for $whereResult argument.

For example, the following excerpt shows code that produces JSON response while adding WHERE position='Software Developer' clause to the query.


echo json_encode(
   SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "position='Software Developer'" )
);

See below excerpt from ssp.class.php for more information on SSP::complex() method.


   /**
    * The difference between this method and the `simple` one, is that you can
    * apply additional `where` conditions to the SQL queries. These can be in
    * one of two forms:
    *
    * * 'Result condition' - This is applied to the result set, but not the
    *   overall paging information query - i.e. it will not effect the number
    *   of records that a user sees they can have access to. This should be
    *   used when you want apply a filtering condition that the user has sent.
    * * 'All condition' - This is applied to all queries that are made and
    *   reduces the number of records that the user can access. This should be
    *   used in conditions where you don't want the user to ever have access to
    *   particular records (for example, restricting by a login id).
    *
    *  @param  array $request Data sent to server by DataTables
    *  @param  array|PDO $conn PDO connection resource or connection parameters array
    *  @param  string $table SQL table to query
    *  @param  string $primaryKey Primary key of the table
    *  @param  array $columns Column information array
    *  @param  string $whereResult WHERE condition to apply to the result set
    *  @param  string $whereAll WHERE condition to apply to all queries
    *  @return array          Server-side processing response array
    */
   static function complex ( $request, $conn, $table, $primaryKey, $columns, $whereResult=null, $whereAll=null )

Using `JOIN` or `GROUP BY` clauses

Solution #1

Workaround described below can be used to add support for any complex query not just JOIN or GROUP BY clauses. However it requires modification of original ssp.class.php class.

Edit ssp.class.php and replace all instances of FROM `$table` with FROM $table to remove backticks. You will be responsible for escaping your table names if they contain reserved keywords.

Use sub-query as table name $table and use any complex query with JOIN, GROUP BY or any other clauses.


<?php

$table = <<<EOT
 (
    SELECT 
      a.id, 
      a.name, 
      b.position
    FROM table a
    LEFT JOIN positions b ON a.position_id = b.id
 ) temp
EOT;

$primaryKey = 'id';

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

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

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

Solution #2

Alternatively you can try forked version of ssp.class.php created by Emran Ul Hadi, see emran/ssp repository on GitHub.

However the file was forked in May 2014, and hasn’t been kept in sync with minor updates to ssp.class.php since then.

Michael Ryvkin

Freelance web developer based in Perkasie, Bucks County, PA. I design and develop custom websites and web applications, perform website maintenance and provide technical support for small and medium-sized businesses.

Contact me for a free quote or consultation on your project.

Comments

David Giorgi says:

September 29, 2017 at 12:32 pm

Brilliant! many thanks for this post

Reply
Konstantinos Chertouras says:

November 18, 2017 at 2:32 pm

Extremely useful , simple in the implementation and working perfectly. Many thanks for the time and effort.

Reply
Duminda says:

December 2, 2017 at 1:55 am

Great post.. Simple short & sweet..

Reply
Tom says:

June 26, 2018 at 7:55 pm

Outstanding! Thanks for the post.

Reply
Ropali says:

January 25, 2019 at 11:04 am

Is #solution1 approach good for production website which may deal with thousands rows?

Reply
1. Michael Ryvkin says:
  
  February 1, 2019 at 9:49 pm
  
  I am using this code on production sites with million rows without any problems.
  
  Reply
Haxor Vince says:

February 5, 2019 at 1:40 pm

Thumbs up 10-Stars to Michael!!!!!!!!!!!!!
You are a time saver thank you for sharing. This is such a simple and straight forward process but its hidden.I wonder why DataTables has not mentioned it. Thanks you for sharing. I have been searching and experimenting with all sorts FORKED nonsense which i was still believed was not the best! THIS IS THE BEST!!!!!!!!!!!!!!!!!!!

Reply
1. Michael Ryvkin says:
  
  February 5, 2019 at 3:00 pm
  
  Thanks, appreciate your feedback!
  
  Reply
Simon Jacko says:

March 4, 2019 at 8:03 pm

Hello Michael, I’m using codeigniter using ssp class solution #1 but I have got an error when using this subqueries.
That the message appear {“error”:”An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1064 blabla blala…bla…

Note : I use same database like your example to test
can you help me, to figure out this?
thanks

Reply
1. Michael Ryvkin says:
  
  March 9, 2019 at 9:09 pm
  
  Have you edited ssp.class.php to replace all instances of FROM `$table` with FROM $table?
  
  Reply
  1. Simon Jacko says:
    
    March 11, 2019 at 5:00 am
    
    Hello Michael sorry for late response…
    Have you edited ssp.class.php to replace all instances of FROM `$table` with FROM $table?
    yes, am I. …
    
    I aready follow your instruction..but not working at all and got that error.
    
    can you help me to solve this?
    or can you make example for me …thanks
    
    Reply
Amran Yobioktabera says:

March 13, 2019 at 11:04 pm

very good post Michael..but, i use it for my 15k data and it’s very slow to show the data..even though i use simple join statement..can u help me please..thanks

Reply
al flores says:

September 13, 2019 at 12:42 pm

Very very very good post, thanks a lot, works great!!

PD. do you know how to put and hiperlink to a column?
🙂

Reply
1. Michael Ryvkin says:
  
  September 14, 2019 at 1:32 pm
  Thanks! Please see columns.render DataTables option on how to render cell data.
  
  For example:
```
$('#example').DataTable( {
  "columnDefs": [ {
    "targets": 0,
    "data": "download_link",
    "render": function ( data, type, row, meta ) {
      return 'Download';
    }
  } ]
} );
```
  Reply
Nate says:

September 15, 2019 at 10:47 pm

Hey Michael,

When using solution #1, I’m getting the error “DataTables warning: table id=stats-table – An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘id’ in ‘field list'”.

The ‘id’ is what I’m using as my $primaryKey. Both tables that I’m querying have a column called ‘id’ as their primary key column. Here’s the whole query:

$table = <<<EOT
(
SELECT
wp_stats_competitor.first_name,
wp_stats_competitor.last_name,
wp_stats_competitor.shooter_id,
wp_stats.office
FROM wp_stats_competitor
INNER JOIN wp_stats ON wp_stats_competitor.shooter_id = wp_stats.shooter_id
) temp
EOT;

When I use this query directly in my database sql program, the query returns correctly. So it seems to be an issue with how datatables is getting the info, and I'm assuming it's something to do with the primary key. Any help would be super appreciated!

Reply
Sunmeet Singh says:

October 2, 2019 at 6:27 am

Thanks sir for this post it really helps me in my projects.

Reply

Can I use a sub-query like this:

SELECT t1.member_name,  t1.product_name, (t1.m1+t1.m2) as this_year,(t2.m1+t2.m2) as prev_year,  ((((t1.m1+t1.m2) - (t2.m1+t2.m2))/(t2.m1+t2.m2)) *100) as difference,
 t1.entry_time as currentyear, t2.entry_time as previousyear   
 from
   (select member_name,m1,m2,entry_time,product_name
   from agreementstest
   where year(entry_time)=YEAR(NOW()))  as t1
   LEFT JOIN
      (select member_name,m1,m2,entry_time,product_name
      from agreementstest
      where year(entry_time)=YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))) as t2
ON t1.member_name = t2.member_name and t1.product_name = t2.product_name

Michael Ryvkin says:

May 20, 2020 at 11:36 am

You should be able to use sub-query as long as you wrap it as ( SELECT ... FROM ... ) AS temp.

Reply

Luke Wakefield says:

June 23, 2020 at 6:07 am

Great stuff. this is how I go it to work in a Laravel controller.

$table =”

(

SELECT

users.name,

tags.name as tag_name

FROM users

LEFT JOIN tags ON users.tag_id = tags.id

) AS temp”;

// Table’s primary key

$primaryKey = ‘uid’;

$columns = array(

array( ‘db’ => ‘name’, ‘dt’ => 0 ),

array( ‘db’ => ‘job_type’, ‘dt’ => 1 ),

array( ‘db’ => ‘matches’, ‘dt’ => 2 ),

array( ‘db’ => ‘tag_name’, ‘dt’ => 3 ),

array( ‘db’ => ‘last_ran’, ‘dt’ => 4 ),

array( ‘db’ => ‘status’, ‘dt’ => 5 ),

array( ‘db’ => ‘uid’, ‘dt’ => 6 ),

);

// SQL server connection information

$sql_details = array(

‘user’ => ENV(‘DB_USERNAME’),

‘pass’ => ENV(‘DB_PASSWORD’),

‘db’ => ENV(‘DB_DATABASE’),

‘host’ => ENV(‘DB_HOST’)

)

echo json_encode(

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

);

Reply
1. Michael Ryvkin says:
  
  June 23, 2020 at 7:22 am
  
  With Laravel I would strongly recommend to use Laravel DataTables where you wouldn’t have to do any workarounds and simply use Eloquent or Query Builder to construct a query.
  
  Reply
Marco says:

July 8, 2020 at 2:02 pm

I would like to thank your article helped me a lot! Thank you.

I have a small problem after editing the ssp.class.php file

FROM `$ table` with FROM $ table

displays this error:

“” DataTables warning: table id = table – An SQL error occurred: SQLSTATE [42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘;
) temp

ORDER BY `id` DESC
LIMIT 0, 10 ‘at line 17 “

Do i need to change any more instances?

Reply
Javier says:

October 2, 2020 at 10:03 am

Thank you!!!

Reply
Rafa says:

November 10, 2020 at 10:44 am

Hallo michael and everyone
i’m get error :
DataTables warning: table id=ordercontoh – An SQL error occurred: SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name ‘(SELECT od.no_order, od.tgl_order, od.design_order FROM order_design od INNER JOIN riwayat_order ro ‘

I use a sub-query like this:

$order_design = “(SELECT od.no_order, od.tgl_order, od.design_order FROM order_design od INNER JOIN riwayat_order ro ON od.no_order = ro.id_order) AS temp”;

           $primaryKey = ‘no_order’;

           $columns = array(
            array( ‘db’ => ‘no_order’, ‘dt’ => 0 ),
            array( ‘db’ => ‘tgl_order’, ‘dt’ => 1 ),
            array( ‘db’ => ‘design_order’,   ‘dt’ => 2 )
            );

           $sql_details = array(
            ‘user’ => ‘root’,
            ‘pass’ => ‘madajaya’,
            ‘db’   => ‘mekarar1_mktapps’,
            ‘host’ => ‘localhost’
            );

           require_once Yii::getPathOfAlias(‘application.extensions.sspclass’) . ‘.php’;


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

Please help me

Reply
1. Michael Ryvkin says:
  
  November 11, 2020 at 2:39 pm
  
  Have you edited ssp.class.php to replace all instances of FROM `$table` with FROM $table?
  
  Reply
Fernando says:

April 6, 2021 at 11:18 am

Perfecto, todo me funciona bien, gracias por compartir.

Reply
1. Michael Ryvkin says:
  
  April 6, 2021 at 10:55 pm
  
  ¡Gracias!
  
  Reply
Alberto says:

April 6, 2021 at 9:09 pm

Generally, I do not appreciate forums that help with programming but, Thank you very much, it worked perfectly for me and it is very easy to understand.

Reply
1. Michael Ryvkin says:
  
  April 6, 2021 at 10:54 pm
  
  Thank you!
  
  Reply
Khawer says:

April 27, 2021 at 3:53 am

Hello Sir! I have followed all steps of sollution1, but getting this error

DataTables warning: table id=my_table – An SQL error occurred: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘user_id’ in ‘field list’

Reply
1. Michael Ryvkin says:
  
  April 28, 2021 at 11:10 pm
  
  Apparently your table doesn’t have user_id column.
  
  Reply
glen says:

September 15, 2021 at 2:06 pm

Hello, does this work with UNION also?

Reply
1. Michael Ryvkin says:
  
  October 3, 2021 at 2:41 pm
  
  Yes, this should work with any SQL statements.
  
  Reply

Using WHERE clause

Using JOIN or GROUP BY clauses