You can go to UPDATE 2 directly

I have a huge table so fetch offset, limit is not going to work as it is taking ages. So, I am thinking to move to key seek paging method so my query is going to be different for each click as mentioned below:

/*First*/
select top (1000) id, name from table_name order by id desc;
/*returns data from 56679923-56678924*/

/*Next*/
select top (1000) id,name from table_name where id < @previous_lowest_id order by id desc;
/*returns data from 56678923-56677924*/

/*Previous*/
SELECT * FROM 
     (select top (1000) id,name 
       from table_name 
       where id > @previous_highest_id 
       order by id asc
     ) as myAlias 
ORDER BY id desc;
/*returns data from 56679923-56678924*/

/*Last*/
SELECT * FROM 
    (select top (1000) id,name
      from table_name
      order by id asc
    ) as myAlias 
ORDER BY id desc;

So, I need to run different queries according to clicked buttons. So, a necessity of detecting different click is raised. If there is any inbuilt method that's great. Otherwise, any other hacks to deal with this situation is also weled.

Furthermore, I can see different id on each li something like

<li class="paginate_button page-item next" id="DataTable_next"> 
    <a href="#" aria-controls="coloradoDataTable" data-dt-idx="2" tabindex="0" class="page-link">Next</a>
</li>

So, I have also tried doing the following but with no success

$('#DataTable_last>a').click(function() { alert('zz'); });

How, can I get rid out from this problem. I am using server-side data table which can be referenced from /

Update:

Now, I could detect the click however I am unable to send this new data to the server. In case, if I set var page = 'first';. This send page as 'first on every request.'

var page;
var dataTable = $('#DataTable').DataTable( {

                drawCallback: function(){
                    $('.paginate_button.first:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'first';
                        console.log(page);
                    });
                    $('.paginate_button.previous:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'previous';
                        console.log(page);
                    });
                    $('.paginate_button.next:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'next';
                        console.log(page);
                    });
                    $('.paginate_button.last:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'last';
                        console.log(page);
                    });       
                },
    "ajax": {
                    "url":base_path +'/other_path',
                    "dataType":"json",
                    "type":"POST",
                    "data":{"_token":csrf_token,"page":page}
                }
        ......
});

These console.log shows the correct page after each button clicked but the value is not sending with other params at the moment.

Update: 2

Currently, my code looks like below. It is sending past page state. This means if I first click next it won't send page but if I now click previous, then this time it will send the page as next instead of the page as previous. This looks like it is sending ajax request first and updating page value later. Now, I just need to fix this means need to update page value first before sending ajax.

var page;

var dataTable = $('#masterDataTable').on('preXhr.dt', function (e, settings, data) {
    data.page = page;
}).DataTable( {

    drawCallback: function(){
        $('.paginate_button.first:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'first';
            console.log(page);
        });
        $('.paginate_button.previous:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'previous';
            console.log(page);
        });
        $('.paginate_button.next:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'next';
            console.log(page);
        });
        $('.paginate_button.last:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'last';
            console.log(page);
        });       
    },

    "processing": true,
    "serverSide": true,
    "searching": false,
    "ajax": {
        "url":base_path +'/other_path',
        "dataType":"json",
        "type":"POST",
        "data":{"_token":csrf_token,"page":page}
    },
    "columns":[
    {"data":"name"},
    {"data":"address"},
    {"data":"city"},
    {"data":"action","searchable":false,"orderable":false}
    ],
    "aLengthMenu": [50, 100, 250, 500],
    "pageLength": 50,

    "ordering": false,
    "pagingType": "full"
} );

You can go to UPDATE 2 directly

I have a huge table so fetch offset, limit is not going to work as it is taking ages. So, I am thinking to move to key seek paging method so my query is going to be different for each click as mentioned below:

/*First*/
select top (1000) id, name from table_name order by id desc;
/*returns data from 56679923-56678924*/

/*Next*/
select top (1000) id,name from table_name where id < @previous_lowest_id order by id desc;
/*returns data from 56678923-56677924*/

/*Previous*/
SELECT * FROM 
     (select top (1000) id,name 
       from table_name 
       where id > @previous_highest_id 
       order by id asc
     ) as myAlias 
ORDER BY id desc;
/*returns data from 56679923-56678924*/

/*Last*/
SELECT * FROM 
    (select top (1000) id,name
      from table_name
      order by id asc
    ) as myAlias 
ORDER BY id desc;

So, I need to run different queries according to clicked buttons. So, a necessity of detecting different click is raised. If there is any inbuilt method that's great. Otherwise, any other hacks to deal with this situation is also weled.

Furthermore, I can see different id on each li something like

<li class="paginate_button page-item next" id="DataTable_next"> 
    <a href="#" aria-controls="coloradoDataTable" data-dt-idx="2" tabindex="0" class="page-link">Next</a>
</li>

So, I have also tried doing the following but with no success

$('#DataTable_last>a').click(function() { alert('zz'); });

How, can I get rid out from this problem. I am using server-side data table which can be referenced from https://datatables/

Update:

Now, I could detect the click however I am unable to send this new data to the server. In case, if I set var page = 'first';. This send page as 'first on every request.'

var page;
var dataTable = $('#DataTable').DataTable( {

                drawCallback: function(){
                    $('.paginate_button.first:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'first';
                        console.log(page);
                    });
                    $('.paginate_button.previous:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'previous';
                        console.log(page);
                    });
                    $('.paginate_button.next:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'next';
                        console.log(page);
                    });
                    $('.paginate_button.last:not(.disabled)', this.api().table().container())          
                    .on('click', function(){
                        page = 'last';
                        console.log(page);
                    });       
                },
    "ajax": {
                    "url":base_path +'/other_path',
                    "dataType":"json",
                    "type":"POST",
                    "data":{"_token":csrf_token,"page":page}
                }
        ......
});

These console.log shows the correct page after each button clicked but the value is not sending with other params at the moment.

Update: 2

Currently, my code looks like below. It is sending past page state. This means if I first click next it won't send page but if I now click previous, then this time it will send the page as next instead of the page as previous. This looks like it is sending ajax request first and updating page value later. Now, I just need to fix this means need to update page value first before sending ajax.

var page;

var dataTable = $('#masterDataTable').on('preXhr.dt', function (e, settings, data) {
    data.page = page;
}).DataTable( {

    drawCallback: function(){
        $('.paginate_button.first:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'first';
            console.log(page);
        });
        $('.paginate_button.previous:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'previous';
            console.log(page);
        });
        $('.paginate_button.next:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'next';
            console.log(page);
        });
        $('.paginate_button.last:not(.disabled)', this.api().table().container())          
        .on('click', function(){
            page = 'last';
            console.log(page);
        });       
    },

    "processing": true,
    "serverSide": true,
    "searching": false,
    "ajax": {
        "url":base_path +'/other_path',
        "dataType":"json",
        "type":"POST",
        "data":{"_token":csrf_token,"page":page}
    },
    "columns":[
    {"data":"name"},
    {"data":"address"},
    {"data":"city"},
    {"data":"action","searchable":false,"orderable":false}
    ],
    "aLengthMenu": [50, 100, 250, 500],
    "pageLength": 50,

    "ordering": false,
    "pagingType": "full"
} );

• javascript
• jquery
• datatables
• pagination

• what is your main problem ? do you have jquery paging problem ? or sql paging problem ? – KumarHarsh Commented Feb 5, 2019 at 11:45
• What does "data": look like on the server side, specifically data.page? Also, it seems like you need to re-bind your dataset after your page changes. – Ross Bush Commented Feb 5, 2019 at 15:29
• @kumarHarsh My main problem is that I am unable to send that updated page variable to the server which is supposed to send either of "First", "Previous", "Next" and "Last" according to the selection of user among this page. For example after first load, if user click on next button on pagination then this page variable should be updated with next valuse and should send it to ther server – Saroj Shrestha Commented Feb 6, 2019 at 7:03
• @RossBush Response have data filled with list of all data fetched from table however, there is no any signs of data.page – Saroj Shrestha Commented Feb 6, 2019 at 7:08
• @RossBush please check update 2 – Saroj Shrestha Commented Feb 6, 2019 at 10:04

3 Answers 3

It should be possible to read the page variable just before the ajax is sent and add this to the request.

Add this after you code:

$('#DataTable').on('preXhr.dt', function (e, settings, data) {
    data.page = page;
})

preXhr Ajax event - fired before an Ajax request is made.

Update: The onclick events seem to run after the ajax-call is constructed. To get around this, onmousedown ja ontouchstart events can be used instead. They should run before the default onclick event

Replace all click jQuery events with mousedown and touchstart:

$('.paginate_button.last:not(.disabled)', this.api().table().container())          
.on('mousedown touchstart', function(){
    page = 'last';
    console.log(page);
});

Update 2:

A more elegant solution to make the click run before the ajax-call and attacht the right page value is to add the listener to the actual link element, not the parent button.

Add the a to the selectors: .paginate_button.first:not(.disabled) a This will make it work.

$('.paginate_button.first:not(.disabled) a', this.api().table().container())          
        .on('click', function(){
            page = 'first';
            console.log(page);
        });
        $('.paginate_button.previous:not(.disabled) a', this.api().table().container())          
        .on('click', function(){
            page = 'previous';
            console.log(page);
        });
        $('.paginate_button.next:not(.disabled) a', this.api().table().container())          
        .on('click', function(){
            page = 'next';
            console.log(page);
        });
        $('.paginate_button.last:not(.disabled) a', this.api().table().container())          
        .on('click', function(){
            page = 'last';
            console.log(page);
        });

After some time I have find the workaround for this type of situation. May be its not the best way, but it does the job.

$(function(){

            var page;
            var HighestID,LowestID;

                document.addEventListener("click", function(e) {
                    if($(e.target).parent().hasClass('previous')){
                        page = 'previous';
                    }else if($(e.target).parent().hasClass('next')){
                        page = 'next';
                    }else if($(e.target).parent().hasClass('last')){
                        page = 'last';
                    }else{
                        page = 'first';
                    }

                }, true);


            var dataTable = $('#Datatable').on('preXhr.dt', function (e, settings, data) {
                data.page = page;
                data.HighestID = HighestID;
                data.LowestID = LowestID;
            }).DataTable( {

                "processing": true,
                "serverSide": true,
                "searching": false,
                "ajax": {
                    "url":base_path +'/admin/other_path',
                    "dataType":"json",
                    "type":"POST",
                    "data":{"_token":csrf_token}
                },
                "columns":[
                {"data":"name"},
                {"data":"address"},
                {"data":"city"},
                {"data":"action","searchable":false,"orderable":false}
                ],
                "aLengthMenu": [50, 100, 250, 500],
                "pageLength": 50,

                "ordering": false,
                "pagingType": "full"
            } );

            dataTable.on( 'xhr', function () {
                var json = dataTable.ajax.json();
                HighestID = json.HighestID;
                LowestID = json.LowestID;
            } );

});

DataTables send start and length in request body, I think it easier to use this, no need to pare what page are and sorter code. for example pageLength is 50 and next or page 2 clicked then DataTables will send body start: 50, length: 50 and your query is like

SELECT id, name
FROM table_name
ORDER BY id DESC
LIMIT @length OFFSET @start /* LIMIT 50 OFFSET 50 */

but if you want to use that page, in beforeSend() function you can try aborting Ajax request if the click event for the button is not yet fired.

var page;
var navClicked = false;
// do not abort ajax for first ajax request or initialization.
var firstLoad = true;
$.ajaxSetup({
  beforeSend: function(jqXHR, settings) {
    // if button click event not yet fired abort ajax
    if (!firstLoad && navClicked == false) {
      jqXHR.abort();
    }
  },
  plete: function() {
    firstLoad = false;
    navClicked = false;
  }
});

then replace drawCallback: function(){...} with

drawCallback: function() {
  $('.paginate_button').on('click', function(e) {
    page = this.textContent.toLowerCase(); // first, previous, next, last
    navClicked = true;
    // 'page' already defined click again this button and do ajax request
    this.click()
  })
},