Pagination without using offsets

Jleagle

(Tried to create this ticket once before but it seemed to disappear?)

So there are some databases where you shouldn't use offsets as it will scan the entire table up until that point.

Instead of:
offset 0 limit 10
offset 10 limit 10
offset 20 limit 10

You just do:
where id > 0 limit 10 order by id asc
where id > 10 limit 10 order by id asc
where id > 20 limit 10 order by id asc

So on the pagination, the next button would be /page?after={lastRowID}
and the previous button would be /page?before={firstRowID}

Is this a feature or possible at all? If not, can you modify the pagination dom at all?

Thanks.

colin

Hi @Jleagle ,

When you have serverSide enabled, DataTables sends an Ajax request to the server to query the database, the pagination looks something like this:

start: 20
length: 10

It's up to your server-side script on how it interrogate the database, provided the returned data is in the expected format. So you can do it anyway you want.

Cheers,

Colin

Jleagle

Hi, I am indeed using server-side scripts.. The trouble with your example is the 20 value is an offset to use on the database query. But you really shouldn't use offsets at all on large databases. To get the 10 rows on page 1 million, you need to scan through 10 million rows in the table, which is slow and expensive. That's why on large tables you just say, select * from large_table where id > ? limit 10 order by id asc. eg. no offset. Thanks.

colin

I don't follow, sorry. In that example, DataTables says I want 10 records, starting from position 20 - all DataTables is doing is requesting the information on the current page.

It's entirely up to your script how you retrieve those records.

Jleagle

I was hoping i would be able to modify the pagination dom to send different values to the server (the first & last row ids). If you have very large tables it's bad practice to use offsets, so the 20 value is useless to me.

colin

That's just not possible - the client can't just ask for the IDs of the first and last row, since it doesn't know the data, all that knowledge is on the server. Bear in mind the table can be ordered by one or more columns, it can be filtered globally or by individual columns too, so all the client can do is send that information to the server, and request a number of rows from a starting point for that criteria...

Jleagle

Thanks, i managed to get the first and last IDs using:

    "ajax": {
        "cache": true,
        "data": function (ajax, settings) {
            var trs = $('.table-datatable tbody tr');
            ajax.first = trs.first().attr('data-id');
            ajax.last = trs.last().attr('data-id');
        }
    },

but would also need the direction the user is moving through the pages, up or down etc. I tried to attach an event handler to clicking on the page buttons and storing their text in a variable but the above function runs before the event runs, so it only ever gets the click before last..

Jleagle

So very hacky, but in the end this worked for me...

var paginationButtonClicked;

$(document).on('mousedown', '.dt-pagination ul.pagination li a', function (e) {
    paginationButtonClicked = $(this).html().toLowerCase();
});

var options = {
    "ajax": {
        "cache": true,
        "pagingType": "full",
        "data": function (ajax, settings) {
            var trs = $('.table-datatable tbody tr');
            ajax.first = trs.first().attr('data-id');
            ajax.last = trs.last().attr('data-id');
            ajax.direction = paginationButtonClicked;
        }
    },
}

kwhat4

@Jleagle Thanks for posting this example, I have been struggling with this for a couple of days now. Have you been able to solve the problem of the pagination page numbers as they relate to the offset and page size? For example, if I am on page 7 of 5 results per page, and I change the page size to 25, it changes my page number to 2. There appears to be a bit more to this problem than just getting the PK ID back to the server when you are using page numbers. There is also the issue of local storage I haven't even started to dig into, but after trying to get a header value from the AJAX response into the data, I think I will find any other solution to this problem. Clearly @colin has never dealt with page 2000 in a 30 million record result set using an offset.

allan

select * from large_table where id > ? limit 10 order by id asc

The problem with this is that the data needs to be only ordered by the id column and it must be sequential with no missing records. If that's what your data set needs for display, then absolutely that would be an extremely fast way of displaying the data.

Equally the solution you came up with @Jleagle is perfect if you can get the next page of data based on the id of the last data point in the currently displayed page. Your database would probably still need to scan the full data set if you have ordering enabled though.

Interest to hear any feedback on this though. My understanding of database internals is far from prefect, and I suspect that there will be database specific extensions that can help with this.

Allan

diego_regini

Hi,
I'm having the same problem, I need paging without offset. Is there a method to get the clicked page direction , from api when ajax is called?

something like this:

ajax: {
            "dataType": "json",
            url: 'ajax/handler.php',
            data : function ( d ) { return $.extend( {}, d, generateTableManagement() )}
}

i can't find in table's api to get the clicked direction

@Jleagle 's method with global variable would work indeed, but i find it more complex to manage for future revisions.

colin

Hi @diego_regini ,

You can get the current page information with page.info(). You could compare that with the values being request within ajax.data to get the direction.

Cheers,

Colin

diego_regini

Hi @colin ,
I'm trying as you suggested but page.info() return the same value of variable d wich is the object sent to serverside.

   ajax: {
            "dataType": "json",
            url: 'ajax/handler.php',
            data : function ( d, settings ) { return $.extend( {}, d, generateTableManagement(d,settings) )},
            type: 'POST',
            dataSrc: 'data'
  }

function generateTableManagement(d, settings){
    var api = new $.fn.dataTable.Api( settings );
    
    
    var values = {};
    values.first_row = getFristRowTable('ma_base_tab');
    values.last_row = getLastRowTable('ma_base_tab');
    
       values.direction = "prev";
    if(d.start < api.page.info.start){//not working they are ===
        values.direction = "next";
    }
    
    return values;
}

function getFristRowTable(id_table){
    if(id_table != ""){
        if(typeof $( "#"+id_table ).dataTable().api().row(0).data() != "undefined"){
            return $( "#"+id_table ).dataTable().api().row(0).data().DT_RowData;
        }
    }
}
function getLastRowTable(id_table){
    if(id_table != ""){
        if(typeof $( "#"+id_table ).dataTable().api().row($( "#"+id_table ).dataTable().api().page.info().length - 1).data() != "undefined"){
            return $( "#"+id_table ).dataTable().api().row($( "#"+id_table ).dataTable().api().page.info().length - 1).data().DT_RowData;
        }
    }
}

Am I missing something?

diego_regini

Resolved, found the api.ajax field:

    if(typeof api.ajax.params() != "undefined"){
        var prev_value = api.ajax.params();
        values.direction = "prev";
        if(d.start > prev_value.start){
            values.direction = "next"
        }
    }