JOIN and extra condition support at Datatables library SSP class

Whenever I need to view some listing in TABLE, I LOVE to use Datatables. Datatables library is really Awesome. It works like charm!!!

If you are new with Datatables then please first check their documentation and examples then come back 🙂

For HUGE data handle their Server side processing works also fine. They provide Server Side Processing class to process the request for datatables @version 1.10.0. But it’s only works with single table :(.

So handle Complex query like join or extra condition not supported at their provided SSP class. After a lots of searching in google and forums not found any reliable solution. After that I changed the class as my own.

I faced problem like

1. Get Data from Multiple table not supported via Joining.
2. Extra Where, except filtering was not possible.
3. During work on Multiple Table, to avoid Duplicate key was not possible.

So due to my purpose done I have changed the SSP class like my own. The changes i made are :

1. I have added option to ADD JOIN Query and make necessary changes.
2. I have changed Column ARRAY format to handle get data from multiple table. And add TWO new index(‘field’, ‘as’) for complex query handle.
3. Add Extra Where condition through SSP Class.
4. You can Group by the result via sending Query through simple function of SSP Class.

You can see the DEMO and DOWNLOAD server-side processing customised SSP class from the following link.

I want to share the changes I made at SSP class and try to explain what & why I made the changes. And I will show the usage too.

Change in SSP::simple Function :

/*

*....

* @param array $columns Column information array
* @param array $joinQuery Join query String
* @param string $extraWhere Where query String
*
* @return array Server-side processing response array
*
*/
static function simple ( $request, $sql_details, $table, $primaryKey, $columns, $joinQuery = NULL, $extraWhere = '', $groupBy = '')
{......
 $where = Libs_SSP::filter( $request, $columns, $bindings, $joinQuery );

// IF Extra where set then prepare query
 if($extraWhere)
 $extraWhere = ($where) ? ' AND '.$extraWhere : ' WHERE '.$extraWhere;

 // Main query to actually get the data
 if($joinQuery){
 $col = Libs_SSP::pluckForJoin($columns, 'db');
 $query = "SELECT SQL_CALC_FOUND_ROWS ".implode(", ", $col)."
 $joinQuery
 $where
 $extraWhere
 $groupBy
 $order
 $limit";
 }else{
.........

Here is the screen shot

SSP simple class

Change in Column Array Filtering :

I have change the column array to fulfill my purpose of joining table. You know column name may duplicatie, So sometimes its needed to rename, I give option to this column array.

1. 1. Index ‘db’ refer which tables which field will select.
   2. Index ‘dt’ refer data table index as before
   3. Index ‘field’ refer at output in which name the output will print from SQL result after rename.
   4. Index ‘as’ refer If this index set then the column name will renamed via the assigned value.  

$columns = array(
array( 'db' => '`c`.`id`', 'dt' => 0, 'field' => 'id' ),
array( 'db' => '`c`.`login`', 'dt' => 1 'field' => 'login' ),
array( 'db' => '`c`.`password`', 'dt' => 2, 'field' => 'password' ),
array( 'db' => '`c`.`name`', 'dt' => 3, 'field' => 'client_name', 'as' => 'client_name' ),
array( 'db' => '`cn`.`name`', 'dt' => 4, 'field' => 'currency_name', 'as' => 'currency_name' )

array( 'db' => '`c`.`id_client`', 'dt' => 5,
'formatter' => function( $d, $row ) {
return '<a href="EDIT_URL"><span class="label label-inverse"><i class="fa fa-edit"></i> Edit</span></a>
<span class="label label-danger pointer"><i class="fa fa-trash-o"></i> Delete</span>
';
}, 'field' => 'id_client' )
);

$joinQuery = "FROM `{$table}` AS `c` LEFT JOIN `currency_names` AS `cn` ON (`cn`.`id` = `c`.`id_currency`)";

$Ssp = new Libs_SSP();
echo json_encode(
$Ssp::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery )
);

Here is the Screenshot

SSP Customized column array

Extra Condition/Where Add :

I have added one option to SSP::simple class as a parameter and i prepare query as it should be. If you need to send some extra data while datatables initialisation, then you can do that like this.

$Where = "`c`.`id_client`=".$CLIENT_ID; // OR For a Normal query condition could be $Where = "`id_client`=".$CLIENT_ID;
$Ssp = new Libs_SSP();
echo json_encode(
$Ssp::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where);
);

Extra where option added.

And how i prepare query for extra where that have shown at first screen shot.

Group By Option :

You can GROUP BY your result via sending the COLUMN name in SSP::simple function.

$groupBy = '`c`.`id_client`'; // for multiple COLUMN '`c`.`id_client`,`c`.`other_param`'
echo json_encode(
$Ssp::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where, $groupBy);
);

In previous version of SSP class, the parameter took full format like below.

$groupBy = ' GROUP BY `c`.`id_client` ';

• HAVING Option

You can use HAVING query within SSP class right now. And it’s pretty easy to use.

 $having = '`c`.`id_client` >= 100'; // same as the extra where
echo json_encode( SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $joinQuery, $where, $groupBy,$having); );

 

• Output Format changes

For JOIN query the output field is mapping from ‘field’ index of COLUMN array rather than ‘db’ index.

Output Mapping from Column field index for JOIN

Other Changes in SSP class :

• filter function
  • Added $isJoin optional parameter.
  • IF $isJoin set then the Quotation have different due to change in db index of COLUMN ARRAY for JOIN.
  • Otherwise it works like before.

• pluck function
  • Same as filter function added $isJoin optional parameter.
  • For rename column name, preparing query is here, If corresponding as index is set at COLUMN Array .

 Updated: Due to few re-factoring and renamed may be some code shown here will mismatch with present version of ssp.customized.class.php. 

Like the Class name is changed SSP from Libs_SSP, So It’s better to see/use Example code at github. You will get just the Idea of changes here, So don’t Use these code blindly.

That’s it…..

So Listing huge data with complex query is FUN with Datatables NOW.

So, Happy Coding!!! 🙂

Any kind of Suggestion or feedback is truly appreciated.