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

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.

You May Also Like

Comments

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

  2. 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!!!!!!!!!!!!!!!!!!!

  3. 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

      1. 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

  4. 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

  5. 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!

  6. 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
    
  7. 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 )

            );

  8. 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?

  9. 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

  10. 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.

  11. 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’

Leave a Reply

(optional)

This site uses Akismet to reduce spam. Learn how your comment data is processed.