How to disable Datatables default count query and enable rows count based on response?

arslanASH

So when datatables run a query let's say,
select * from users where users.number is not null
it actually runs two query:
first is the actual query and second is the query to get the total number of results so because of this, there are 2 requests getting made to the DB.
But i do not want this, what i want to do is just count the number of rows returned from the actual query instead of running a separate query to get the rows count.
Also, i checked datatable options and there is no way to do that through configuration as well.

Here is my configuration:

                "processing": true,
                "serverSide": true,
                "searching": true,
                "pageLength" : 100,
                "destroy": true,
                "order" : [],
                "resetPaging": false,
                language: {
                    "processing": "Loading. Please wait..."
                },
                ajax: {
                    url: 'xyz',
                    data: function (d) {
                    }
                },
                columns: dataArray,
                'columnDefs': [ {
                    'targets': 'datatable-nosort', /* column index */
                    'orderable': false, /* true or false */
                }],
                "initComplete": function(settings, json) {
                }

chickenTikka

Getting the same,
try to learn PHP.

chickenTikka

"processing": true,
"serverSide": true,
"searching": true,
"pageLength" : 100,
"destroy": true,
"order" : [],
"resetPaging": false,
language: {
"processing": "Loading. Please wait..."
},
ajax: {
url: 'xyz',
data: function (d) {
}
},
columns: dataArray,
'columnDefs': [ {
'targets': 'datatable-nosort', /* column index /
'orderable': false, / true or false */
}],
"initComplete": function(settings, json) {
}

chickenTikka

say bow bow

kthorngren

what i want to do is just count the number of rows returned from the actual query

The server side processing protocol expects a recordsTotal parameter which should the total number of rows in the database. The recordsFiltered is the total number of rows after filtering.

If you just count the number of rows returned form the query you will only get the number of rows on the page. The server side processing query should result in only those rows (10 by default) displayed on the page and these are returned to the client. The `recordsTotal will then be 10 and the paging buttons will only allow for paging to the first page.

Kevin

arslanASH

Hi Kevin, so yes my json returned response does contain recordsTotal and recordsFiltered but the thing is to get to know recordsTotal and recordsFiltered, datatables run a separate query.
But what I want is that datatables should only run the actual query and not run any more query to get the count.

kthorngren

not run any more query to get the count.

How do you want to get the total query? Datatables doesn't know anything about the size of you DB so the query to get the total DB row count is needed. Is there a problem with this query running with your DB?

Kevin

arslanASH

Hi kevin actually yes there is a problem so basically these queries are getting run very often and this is actually impacting our db as well sometimes taking the db down as well. so I wanted was just return the data and I will count the data length myself using simple for loop.

Also if my question was not clear, here is the better explanation:

Those who have experience with DataTables may have already known that, to make the client-side pagination work properly, jQuery DataTable required two parameters: recordsTotal & records filtered. Now this recordsFiltered is expecting the total number of filtered records without the pagination LIMIT.

Which in other words means that I have to run a query without the LIMIT keyword and get the count of the number of rows in that result. Then I have to execute the same query with LIMIT keyword to fetch the actual data. So, technically I just wrote two queries right now, one with LIMIT and one without LIMIT and executed both (However, first one just took the number of rows instead of the whole result).

What I want Datatables to do is just not run the count query, I will get the count myself. Is that somehow possible and if it is then will it affect pagination?

kthorngren

Which in other words means that I have to run a query without the LIMIT keyword and get the count of the number of rows in that result.

What DB are you using? Does it support something like the SQL count() function. Sounds like you are simply selecting all the rows from the DB then in your server script getting a count of those rows. This would be inefficient. Using a query function for counting the rows should be optimized by the DB language and very efficient.

What I want Datatables to do is just not run the count query, I will get the count myself. Is that somehow possible

The server script is something you control. Whether it came from Datatables, another source or you wrote it. You can count the rows in anyway you want. You just need to customize the server script.

if it is then will it affect pagination?

Pagination relies on accurate recordsTotal values. If its incorrect then it will affect pagination.

Kevin

arslanASH

Hi Kevin
so to get recordsTotal, datatables will run extra query and then based on recordsTotal, it will determine pagination.
This is what is happening.
But i want is to somehow change this recordsTotal to simply length of returned response data and not to be another query on db.

kthorngren

As I said you can customize the server script to calculate the recordsTotal in any way you want. You can simply return a hard coded value if you want. The paging buttons and the info displayed might not be accurate.

You can customize the paging controls with a paging plugin.

Kevin

allan

But i want is to somehow change this recordsTotal to simply length of returned response data

That won't allow DataTables' paging to work though. It knows how many records you are returning - that's a simple .length call. The extra information is needed so it can display the paging control and allow the table's paging to be changed (i.e. it needs to know there are 1000 rows, or whatever, in the table).

Allan