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

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) {
}
Answers
Getting the same,
try to learn PHP.
"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) {
}
say bow bow
The server side processing protocol expects a
recordsTotal
parameter which should the total number of rows in the database. TherecordsFiltered
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
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.
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
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:
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?
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.
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.
Pagination relies on accurate
recordsTotal
values. If its incorrect then it will affect pagination.Kevin
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.
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
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