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"
} );
Share
Improve this question
edited Feb 6, 2019 at 10:28
Saroj Shrestha
asked Jan 29, 2019 at 12:56
Saroj ShresthaSaroj Shrestha
2,8956 gold badges23 silver badges47 bronze badges
7
- 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
Reset to default 1It 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()
})
},