The value of iDisplayLength not increasing with Server-side processing

mkhan1484

I have recently started using this tool for displaying data and it is a great tool with lots of flexibility. However, I have come across an issue that I need some help with:

I have got 20 records in the database table and the data tables is showing me the first 10 records upon loading the page and it tells me that "Showing 1 to 10 of 20 entries". But when I click on the "Next" button, it tells me that there are no records to display. In order to find out what is happening, I started dumping the parameters in the request object on the server side and I noticed that the value for "iDisplayLength" is always 10 regardless of whether I click on Page 1 or Page 2. So when I am on page 2, the limit clause becomes "SELECT .... LIMIT 10, 10" and, therefore, not returning any records. Hence datatables says "no records to display"

Note: I have already searched through the user forum and found http://www.datatables.net/forums/discussion/3412/idisplaylength-is-not-changing-in-pipeline/p1 but it did not help me.

Here is my javascript code:

/*
This function serves as a plugin for DataTables to delay sending ajax call every time
user types in a character.
http://datatables.net/plug-ins/api#fnSetFilteringDelay
*/
jQuery.fn.dataTableExt.oApi.fnSetFilteringDelay = function ( oSettings, iDelay ) {
var _that = this;

if ( iDelay === undefined ) {
iDelay = 750;
}

this.each( function ( i ) {
$.fn.dataTableExt.iApiIndex = i;
var
$this = this,
oTimerId = null,
sPreviousSearch = null,
anControl = $( 'input', _that.fnSettings().aanFeatures.f );

anControl.unbind( 'keyup' ).bind( 'keyup', function() {
var $$this = $this;

if (sPreviousSearch === null || sPreviousSearch != anControl.val()) {
window.clearTimeout(oTimerId);
sPreviousSearch = anControl.val();
oTimerId = window.setTimeout(function() {
$.fn.dataTableExt.iApiIndex = i;
_that.fnFilter( anControl.val() );
}, iDelay);
}
});

return this;
} );
return this;
};

$(document).ready( function() {
var oTable = $("#id_pr_items").dataTable({
//"iDisplayLength": 10, // Default No of Records per page on 1st load
//"aLengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]], // Set no of records in per page
"bJQueryUI": false, //do not use the JQuery ui
"bProcessing": true,
"bAutoWidth": false,
"sPaginationType": "full_numbers",
"bServerSide": true,
"sAjaxSource": "../pr_items_json/",
"bStateSave": false, // Remember paging & filters
"oLanguage": {
"sSearch": "Search entire table",
"sProcessing": " Loading data..." ,
"sZeroRecords": "No records to display"
},
"sDom": '<"top">flt<"bottom"irp><"clear">', //re-arrange where search, pagination, etc shows up
"aoColumnDefs": [
{"sWidth": "60px", "sType": "numeric", "bSearchable": false, "bSortable": false, "aTargets": [0]},
{"sWidth": "70px", "sType": "string", "assorting": ["asc"], "aTargets":[5]}
],
"aoColumns": [
{"mData": "pr", "mRender": function(data, type, full)
{
return data.pr_num_part_one + "-" + data.pr_num_part_two;
}
},
// When the server returns an JSON array of objects instead of JSON array of array,
// then DataTables require that column names be defined as below
{"mData": "description"},
{"mData": "quantity"},
{"mData": "unit"},
{"mData": "actual_price"},
{"mData": "fund_code"},
{"mData": "created", "mRender": function(data, type, full)
{
var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
var created = new Date(data);
var hours = created.getHours();
var am = hours < 12 ? "AM" : "PM";
hours = (hours % 12) || 12;
var time = hours + ":" + created.getMinutes() + ":" + created.getSeconds() ;
created = (monthNames[created.getMonth()])+" "+created.getDate()+", "+created.getFullYear() + " at " + time + " " + am;
return "

mkhan1484

Here is the datatables debugger link: http://debug.datatables.net/efahiw

jsmith88

[quote]I have got 20 records in the database table and the data tables is showing me the first 10 records upon loading the page and it tells me that "Showing 1 to 10 of 20 entries". But when I click on the "Next" button, it tells me that there are no records to display. In order to find out what is happening, I started dumping the parameters in the request object on the server side and I noticed that the value for "iDisplayLength" is always 10 regardless of whether I click on Page 1 or Page 2.[/quote]
Well, nothing seems out of place here. Note that in the documentation, iDisplayLength says it is the "[n]umber of rows to display on a single page when using pagination." So, iDisplayLength should always be 10.

Here are some helpful settings to look for:
iDisplayStart -- this will tell you the current offset (should be 0 on page 1, 10 on page 2, etc...)
iRecordsTotal -- how many rows total, excluding pagination
iDisplayLength -- how many rows to show per page
aiDisplay.length -- how many rows are being displayed on the current page; suppose you had 25 records total and were on page 3 -- aiDisplay length should be 5, because it's showing records 20-24


[quote]So when I am on page 2, the limit clause becomes "SELECT .... LIMIT 10, 10" and, therefore, not returning any records. Hence datatables says "no records to display"[/quote]
Again, I'm not seeing a problem here.

Note that in Oracle/MySQL, LIMIT $X, $Y is the equivalent of saying, "select $Y records, using a start offset of $X." In other words, "SELECT ... LIMIT 10, 10" will grab records 10 - 19 (assuming a start index of 0).

If you're using something like TSQL (Microsoft's implementation of SQL), that query won't work. While similar, TSQL has slightly different ways of doing things than MySQL/Oracle.

So, the query that Datatables is generating for you: the LIMIT statement is being generated using iDisplayStart (10 on page 2), and iDisplayLength.

i.e... "SELECT ... LIMIT 10, 10" == "SELECT ... LIMIT $iDisplayStart, $iDisplayLength"

mkhan1484

Thanks much for your reply and clarification on iDisplayLength. I have the problem on the server side by
if iDisplayStart == iDisplayLength:
iDisplayLength = iDisplayStart + iDisplayLength

Then making the queryset as: queryset = res.obj_get_list(request_bundle)[iDisplayStart:iDisplayLength]

I used django (a python framework) on the backend. So I don't deal with SQL statements directly. I was just mentioning that to clarify what it does.