Getting the data of the clicked page

saibird

Hello,

I have been playing around with DataTables a bit, but of late to fix some performance issues I am looking at implementing a pagination method with the large amount of data i have to deal with. So I have about 100k records in my database, i retrieve only 500 records at a given time, when the user clicks on the last page of the table the next 500 records are retrieved. Now i have been able to enable the retrieval of small chunks but when i try to use the table.row(":last", {order:"applied"}).data() i am unable to get the data of the page that was clicked but instead i get the data of the previous page that i was on. Does anyone know how i can retrieve the very last row on the page i just clicked or of the very last row among all the rows that exist in the table currently?

colin

Hi @saibird ,

It depends when you're getting that last row - if it's before the draw, then I suspect it would be the previous page, if after, then yep, it should be current page. If you could link to your page, or create a test page, we can take a look.

If you haven't seen serverSide, it would be worthwhile seeing if this would help. This is designed for exactly what you're after - sending back small pages of a very large dataset.

Cheers,

Colin

saibird

Hey Colin,

Yes i have looked at the serverSide option, I haven't been able to use it very well for my given scenario though. So here is my code snippet for the way i am using the get page data feature i mentioned above.

tabletest = $('#ctable').DataTable({
order [0, 'asc'],
columns: [
{title: 'Id'},
{title: 'Name'},
{title: 'Age'}
],
deferRender: true,
});

$('#ctable').on('page.dt', function(){
var table = $('#ctable').DataTable();
var table_info = table.page.info();
if(table_info.end == table_info.recordsTotal){
var last_id = (table.row(":last", {order: ' applied}).data())[0];
getNextchunk(renderChunk, last_id);
}
}

I have also tried replacing the var last_id = (table.row(":last", {order: ' applied'}).data())[0]; with var last_id = (table.row(":last", {page:'current', order: 'applied'}).data())[0]; and i get undefined in several occasions and the value in certain ones.

What i am trying to do here is basically get the last id on the table when i click on the last page number, and then pass that id to my ajax call in order to get records associated with ids greater than that last_id value and add them to the table.

kthorngren

Sounds like the problem may be how you are populating the table. Are you using Datatable's APIs or some other method to populate the table. If some other method then Datatables probably doesn't know about the updated data. You can use something like rows().invalidate() to have Datatables refresh it's cash. Or use clear(), rows.add(), draw() to have Datatables update the table.

Kevin

saibird

Hey Kevin,

I have an Ajax call that retrieves the json data, parses it and the populates my table using the .row.add() and .draw(false) methods. Is that what you mean by the Datatables API? If there is a better way to populate the tables i would prefer to use that.

I would also like to share how i am using the datatables: I have about 10 tables on my webpage, these tables all have values being written to them. I have 2 ways of updating the tables,
1. As and when new records are populated into my database, i update the tables and add this data to the beginning of the table.
2. When the user requests older data by clicking on the last page, I retrieve older data and append them to the table.

Is there a better way of doing this for all the tables using some datatables APIs?

Thanks
sai

colin

Hi @saibird ,

That does an unusual way to populate the table, but, if it works, that's the main thing.

I'm not sure why the last row isn't being displayed. If you could create a test case, or link to your page, we'd be happy to take a look. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

saibird

Hey,

So here is a test case which has my code snippet, in this case it works fine but in my production usage it keeps getting the first value of the table no matter how many pages i scroll through. And if i ever navigate to a particular page number I see undefined being retrieved by the .page.info() method. Also as a result of this i am always adding duplicate data into the table which defeats the purpose of trying to paginate through the data. Any help is appreciated.

live.datatables.net/papicipi/1/watch?html,css,js,output

Thanks
sai

kthorngren

Thanks for the test case. Took a look and not sure exactly what you are after but it seems what you want to is have an extra row added that will produce the next paging button. In your test case you are adding 6 rows each time a new page is added. There is a flaw with this as you end up with more data than you are expecting.

I think what you want is to add 6 rows for the initial table load then only add 5 for each page that is added. This example does that:
http://live.datatables.net/jilofuco/1/edit

I added a boolean variable (first) that is true on the initial load then false after. When true 6 rows are added. When false 5 rows are added.

Is this what you are looking for?

Kevin

saibird

Thanks for the response, so yes you are partly right. What i am exactly trying to achieve is this:
1. On initial page load, an ajax call is made to retrieve the 500 most recent data from the database. This data is returned as a JSON string which is parsed and each row is added to the datatables, that leaves me with 50 pages on the datatable scrolling view (viewing 10 records per page).
2. Now the user clicks the next button, after landing on the 49th page, if the user clicks next, the next chunk of most recent 500 records starting from the timestamp on the last record on the 50th page, are retrieved. This is another ajax request.
3. I want the returned values to get appended to the end of the table and thus resulting in 100 pages, now again when i click on the next once i am on page 99 i want the next 500 records to be retrieved from the datatbase starting from the timestamp of the last record on page 100.

I am trying to do this because my database have over a million records and i dont want to slow the page down by retrieving all million records.

kthorngren

Not sure if this will help but if you use serverSide processing you could implement pipelining as showing in this example:
https://datatables.net/examples/server_side/pipeline.html

Kevin