Disable count query but use the paging feature -> serverside

arslanASH

So basically, my configuration are:

"processing": true,
                "serverSide": true,
                "searching": true,
                "pageLength" : 100,
                "destroy": true,
                "order" : [],
                "resetPaging": false,
                // "paging": false,
                // "info": true,

Now the thing is whenever I use this to load any data, two queries get run as follows:

{query: 'select count(*) as aggregate from (select `u`.`nam…s null) order by `ocr`.`id` desc) count_row_table', bindings: Array(6), time: 2811.97}
{query: 'select `u`.`name` as `agent_name`, `ulc`.`purchase…null) order by `ocr`.`id` desc offset 0', bindings: Array(6), time: 947.28}

This is how the query overall looks in the returned json object so as you can see datatables is running two queries, first is count query and second is actual query.

The first query is used for paging purpose i get that but i don't want it to run a separate sql query just for that, is there no way to just run the second query ( actual query ) and somehow set the total length by just doing .length and make the pagination work?

Also, right now to solve this, i am just hard coding the number by using
setTotalRecords and i have to keep this as high number so as to mess up pagination

kthorngren

Allan answered your same question in your other thread. The problem is that if you do some filtering of the table and return a subset of the rows this won't work properly as you won't be able to get the total number of rows.

This seems like an overly complex query to count the rows in the table:

'select count(*) as aggregate from (select `u`.`nam…s null) order by `ocr`.`id` desc) count_row_table'

Take a look at the Datatables ssp.class.php, which is the script used in the SSP examples, to see how it gets the total rows. For example:

        // Data set length after filtering
        $resFilterLength = self::sql_exec( $db, $bindings,
            "SELECT COUNT(`{$primaryKey}`)
             FROM   `$table`
             $where"
        );
        $recordsFiltered = $resFilterLength[0][0];

        // Total data set length
        $resTotalLength = self::sql_exec( $db,
            "SELECT COUNT(`{$primaryKey}`)
             FROM   `$table`"
        );
        $recordsTotal = $resTotalLength[0][0];

Maybe you just need something like this:

select count(u.nam) from count_row_table

Kevin