jquery datatables filtered pagination

Question

Using iTotalRecords and iTotalDisplayRecords with filtering does not seem to work with pagination and table display length

Here is my datatables js code:

transTable.dataTable({
    "aaSorting": [[0,"desc"]],
    "sPaginationType": "full_numbers",
    "bJQueryUI": true,
    "aLengthMenu": [[10, 25, 50, 100, 200, -1], [10, 25, 50, 100, 200, "All"]],
    "iDisplayLength": 10,
    "bProcessing": true,
    "bServerSide": true,
    "sAjaxSource": "",
    "sServerMethod": "POST"
    "aoColumns": [
        { "mData": "datetime"},
        { "mData": "trans"},
        { "mData": "type"}
    ]
});

When I first load the JSON (just POSTs to same page), the pagination works and displays correctly as does sorting. My default display per page is 10.

However, when I filter using the search box, the relevant POST params are:

{  ...
  iDisplayStart: 0
  iDisplayLength: 10
  sSearch: searchText
  ...
}

The SQL query run is:

SELECT 
* 
FROM 
trans 
WHERE 
  (type LIKE "%searchText%") OR 
  (trans_id LIKE "%searchText%") OR 
  (datetime LIKE "%searchText%") 
ORDER BY "datetime" DESC
LIMIT 10 -- 0 is iDisplayStart so no OFFEST set here and 10 is iDisplayLength

My json response is as such:

{
  'aaData': [the query result rows],
  'iTotalRecords: 10000, //total number of records in the table
  'iTotalDisplayRecords: 10,
  'sEcho': //POST params "sEcho" value
}

Because there is a LIMIT 10 on the query, iTotalDisplayRecords will be 10.

You would think the pagination would say "Displaying 1 to 10 records of 1,234 (filtered from 10,000) and allow pagination through the 1,234 records (123 pages). But no. It says: "Displaying 1 to 10 records of 10,000" with no pagination.

If I get rid of the iDisplayLength, the pagination text looks correct and the pagination works correctly, however the table is displaying all 1,234 records, not just 10.

What am I doing wrong?

Leye Eltee Taiwo Leye Eltee Taiwo · Accepted Answer · 2017-05-24T19:21:07

IIRC, iTotalDisplayRecords should be the number of records that are part of your filtered set (1,234).

I set up my paginated datatables by using output parameters in my sql to count the total number of records in my database (10,000) and the number of filtered results (1,234) then pass that in my json (together with the data and the number of draws) as iTotalRecords and iTotalDisplayRecords respectively.

Screenshot of implementation

jquery datatables filtered pagination

1 Answers