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

supermind

After reading this discussion, I have hard time understanding why recordsTotal is needed for pagination? However I understand that recordsFiltered is needed, of course.

When I debug SQL queries, I see three coincident queries:

1. select count(...) from ... where ... [with 'where' condition, to get recordsFiltered]
2. select count(...) from [without 'where' condition, to get recordsTotal]
3. select ... from ... where ... order by ... offset ... [with the same 'where' condition as in step 1 to get the actual data]

Step 1 is needed to get the row count needed for pagination and step 3 is needed to get the actual data.

But, is step 2 needed only to show the total counts WITHOUT filtering? If so, there should be an option to disable that if it is no related to pagination at all.

Our database has tables that have millions or billions of rows and we rarely query data without filtering, so it's unnecessary to know how many many rows there would be without filtering. Query used in step 2 is also very resource hungry, because it uses no filtering and databases tend to to full index scan querying number of rows from the table using no where-conditions in indexed columns.

@allan, what are your thoughts on this? Is there a way to disable step 2 (recordsTotal)? We're using your .NET server side code.

allan

I see what you mean - apologies that I wasn't quite following along before. You are correct - only the filtered total is actually required. The complete row count isn't used for pagination but simply for the information display.

There isn't a way to disable that read in our server-side libraries at the moment. You could if you wanted, change this piece of code to remove the full record count calculation and just assign it the same as the paged count.

Allan

supermind

Great that it is possible by tweaking server-side libraries. I'd love to have this feature to be set with parameter in Editor instance.

If I think more about what would be most powerful way to implement pagination is that server-side would return the actual results immediately and then running query for recordsFiltered afterwards. Then, user would see the actual data as soon as possible and pagination controls could be passive (visually like "waving" progressbar) until recordsFiltered query result is ready.

Server-side code could totally avoid the recordsFiltered query if returned row count is less than page size. Actually, in our case that is usually true in many cases.

allan

Yes, if you happen to be on the last page of data, then yes, the filter could be skipped. I'd have said that is not a common state to be in with server-side processing enabled though.

Actually, in our case that is usually true in many cases.

Server-side processing is only really useful if your client-side display has tens of thousands or more rows. It doesn't matter how larger the data set at the server-side is. If it is only going to return a handful or rows, disable server-side processing and just let the client-side page and search the data.

I presume you have some filter parameter that is reducing the full data set to a much smaller sub set?

Allan

supermind

Yes, we have filtered views that are saved, and user can change the filter as they will of course. Example:

• there are billion rows without filtering
• user has filtered the table so that there are only 25 rows returned
• view is then saved
• when user returns to the page, saved view is loaded (and unfiltered query is never executed)

So, there is no need to know that there is billion rows in the database table and if the page size is set to more than 25 rows there is no need to know recordsFiltered, because the table already has all rows displayed.

kthorngren

Just to make sure I understand correctly:

1. The ajax request from Datatables always requests a filtered view
2. The filtered view returns max 25 rows of data
3. When the user change views you use something like ajax.reload() to fetch a new set of filtered data

If this is correct then, as Allan said, you don't need server side processing enabled. You can just perform the data query, ie:

3. select ... from ... where ... order by ... offset ... [with the same 'where' condition as in step 1 to get the actual data]

Kevin

supermind

We use SearchBuilder extension and the result may contain any number of rows (between 0 and billions), depending filters used.

25 rows mentioned above was just an example. I'm trying to point out that when using server-side processing, there are cases where you don't need two queries when doing pagination (when returned rows in the last or only page is less than page size).

kthorngren

Sorry, I misunderstood. Sounded like the max rows any filter would return is 25. Probably the easiest and quickest thing to do is to update the code as Allan suggested. Allan can comment on the feasibility and timeframe of making changes to allow controlling these queries.

Kevin