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?

arslanASHarslanASH Posts: 5Questions: 2Answers: 0

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

  • chickenTikkachickenTikka Posts: 3Questions: 0Answers: 0

    Getting the same,
    try to learn PHP.

  • chickenTikkachickenTikka Posts: 3Questions: 0Answers: 0

    "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) {
    }

  • chickenTikkachickenTikka Posts: 3Questions: 0Answers: 0
    edited June 27

    say bow bow

  • kthorngrenkthorngren Posts: 16,447Questions: 25Answers: 3,898
    edited June 27

    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

  • arslanASHarslanASH Posts: 5Questions: 2Answers: 0

    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.

  • kthorngrenkthorngren Posts: 16,447Questions: 25Answers: 3,898

    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

  • arslanASHarslanASH Posts: 5Questions: 2Answers: 0

    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?

  • kthorngrenkthorngren Posts: 16,447Questions: 25Answers: 3,898

    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

  • arslanASHarslanASH Posts: 5Questions: 2Answers: 0

    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.

  • kthorngrenkthorngren Posts: 16,447Questions: 25Answers: 3,898

    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

  • allanallan Posts: 56,483Questions: 1Answers: 8,964 Site admin

    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

Sign In or Register to comment.