Paging Server Side Data On Client

paulie

Hi,

Firstly thanks for a great plugin. Really fantastic!

I'm retrieving data from the server and displaying all data in a single table. At the moment I return a maximum of 100 rows from the server. How can I get the client to handle the paging so creating 10 pages of 10 results each? It seems that it wants to handle the paging on the server but that isn't necessary for my requirements. I've looked at pipelining but that seems overkill for me, I'm happy to return the 100 results in one go.

Thanks,
Paul

allan

Hi Paul,

Not quite sure what you mean - is there more than 100 records in the database, but you want to only return 100 at a time, and then have DataTables do the paging? This isn't directly possible at the moment - the data source model that DataTables uses assumes that either all of the data is in the client, or it's all at the server - and it can get it all either way. The pipelining example sounds close to what you want - but requires server-side processing, and this can be done due to the assumption that all the data is on the server (it puts itself in the middle).

I suspect it would be possible to hack DataTables to do what you are looking for, but one thing to consider is that filtering, sorting etc will need to be done with all records - so you've got server-side processing requirements anyway. Might as well just go the whole hog :-)

Regards,
Allan

paulie

Hi Allan,

Thanks for replying. The server has returned all the data to the client in a single call. Since that call can return upto 100 records I just want to do clientside paging of the records. So to answer your question the client has all the data it requires I just want to page to improve user experience rather than for any performance reasons.

Thanks,
Paul.

allan

Hi Paul,

Got it - thanks for the clarification. In that case you can use sAjaxSource (without enabling server-side processing) to ask DataTables to grab the entire data set and the display it with paging. It will also add sorting and filtering by default, but that can be disabled if you want.

This example might help: http://datatables.net/examples/data_sources/ajax.html

Regards,
Allan

paulie

Hi Allan,

Hmm, I don't seem this get the behaviour described. This is the setup for the table:

oTable = $('#example').dataTable( {".
'"bProcessing": true,
"bServerSide": true,
"bPaginate": true,
"sPaginationType": "two_button",
"bSort": false,
"aoColumns": [
null,
null,
null,
null,
null,
{ "bVisible": false },
],
"sAjaxSource": "/my_source",
"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
$("td", nRow).click(function() {
// hook up some click handlers here
});
return nRow;
}
} );

Is there anything obvious that I'm missing to get pagination working?

At the moment the table shows all 100 hundred results with a message at the bottom saying "Showing 1 to 100 of 100 entries (filtered from 5 total entries)".

Thanks,
Paul.

paulie

Ahh, I think I understand now. If I set "bServerSide" to false then the first time the table loads it requests the data from the ajaxsource. But I'm using column filters and when I type in the filter I want to go back to the server, get the data back to the client and then page the data on the client.

I've found that when I disable bServerSide subsequent calls to "_that.fnFilter( searchTerm, tableHead.index(inputControl) );" don't call through to the server. I presume that's because it's attempting to do clientside filtering. Is that correct?

It seems that bServerSide is probably controlling too many aspects. I think filtering server side is fairly common, especially when the underlying dataset is significant. Could this be separated from the paging though? Server side paging is done for quite different reasons than client side paging and it seems we can't distinguish these at the moment.

Sorry if I've missed the point!

Thanks,
Paul

allan

Hi Paul,

The behaviour of DataTables interaction with the server-side depends entirely on the bServerSide value:
- When true, all data processing is done by the server-side. This includes paging, filtering, sorting etc. In this mode, sAjaxSource is where it sends the XHR to get the data on each and every draw.
- When false, all data processing is done by the client-side. In this mode sAjaxSource is where DataTables will load the entire data set from, in order to do the processing on the client-side.

I'm not sure I understand how you would filtering on the server-side but pagination of the client? Assuming you have 1 million rows in a database (I've heard of DataTables being used with 20 million+ rows), you wouldn't want that to be processed on the client-side - that's what the SQL engine is tuned for, so it can do the heavy lifting. Filtering, sorting and paging are all intrinsically linked - change one and you might have to change another.

What I think you could do to achieve what you are looking for, is to make use of the fnReloadAjax plug-in. When the search term is entered, you can call this plug-in and have it reload your data for you (with any additional filtering terms you want to add to the request) and then let DataTables do the paging, client-side filtering of the data set, sorting etc as needed.

Regards,
Allan

paulie

Hi Allan,

I'm possibly missing something. Filtering the underlying data using SQL seems to be a given. I limit the number of records returned simply because the filters that can be applied can be very generic e.g. the letter "a". I always limit the number of returned rows to 100. If the user wants more specific results they type in more letters!

Nevertheless to improve the user experience I want to page that 100 results on the client side. Generally server side paging is tweaked until a balance has been found that allows for optimal performance for bringing back "x" number of records, client side paging is purely about making that amount of data manageable for the end user. For example if I think that 10 records is a reasonable amount of data to show my users per page then currently I seem to have to call the server each time to get 10 records, performance wise that's far worse (generally) than bringing back 100 records in a single call and then paging it on the client.

I'll try what you suggest with fnReloadAjax and see how I get on.

Thanks for bearing with me!
Paul

allan

Hi Paul,

I think I see what you are getting at now! Have a look at this example of server-side processing with pipelining: http://datatables.net/examples/server_side/pipeline.html

What is does, and what I think you are more or less after, is to pull a larger set of records from the database, than can be shown on a single page. There is a 'cache' array which stores the next few pages (it's adjustable so if you want 100 records at a time, that's fine), so up to a point the pagination could be considered client-side (sine there are no XHRs), but whenever an action like sorting or filtering occurs, the pipeline is cleared and a request is set to the server.

You might also be interested in this plug-in: http://datatables.net/plug-ins/api#fnSetFilteringDelay - which will add a small delay to the filtering, which can help stop your own pages killing your server!

Hope this helps!

Regards,
Allan

paulie

Hi Alan,

Thanks, I'll give it a shot.

I'm already using a heavily customised version of fnSetFilteringDelay to do column level filtering. I'm happy to share the code if you think people would be interested. What would be the best way of sharing it?

Paul.

allan

Hi Paul,

All improvements to the plug-ins are very welcome! You could either post it here, or send it to me direct using http://datatables.net/contact . The one thing about column only filtering is that DataTables doesn't have any control over the input elements, so it might be quite hard to make a generic filter which accounts for the various set ups that can be done (i.e. filtering only columns 2 and 4, but with the input elements in a div above the table rather than in the footer - etc)! But the fnSetFilteringDelay plug-in is certainly one of the more popular ones, so any enhancements would be greatly welcomed :-)

Regards,
Allan

paulie

Hi Allan,

I'll pop it over to you. I think I've got round the generic problem by just specifying the selector for jquery. In my case the input elements are in thead with a given class. Passing through the selector to my function lets me put the inputs anywhere. Seems to work for me. I "get by" with jquery though, it's certainly not my strongest point!

Paul.

busyoonhi@yahoo.com

I had similar problem. I wanted to get data from server side regularly ( not just once in the beginning) but wanted to do the pagination/filtering on the client side. With bServerSide set to true it's not possible. So ended up using extra plugins e.g. uiTableFilter and datasorter to filter/sort the data on the current page ( it does not filter/sort all the records and just filters the records on the current page ) . I wish there was an option to get the data from server side but do the sorting/filtering on the client side with whatever data received from the server.

allan

There is an option for this - use sAjaxSource: http://datatables.net/examples/data_sources/ajax.html and make use of the fnReloadAjax plug-in http://datatables.net/plug-ins/api#fnReloadAjax . Then data is loaded from the server, but DataTables will do all the hard work. If you want to add parameters on what is sent to the server, or post-process the return, you can use fnServerData ( http://datatables.net/examples/server_side/custom_vars.html ) - which works for non-server side processing in v1.6 now.

Allan

busyoonhi@yahoo.com

Hi Allan,
Thanks for the reply.
How may I invoke the fnReloadAjax function ? I would like to invoke it when any of the pagination settings changes ( e.g. user clicks on the next/previous buttons or changes the display length).

Thanks,
busyoonhi

busyoonhi@yahoo.com

Hi Allan,
I am trying to get the data from Server using the ajax option ( i.e. bServerSide set to false ). I set the iTotalDisplayRecords field in the JSON to 120 while the actual JSON contains 20 records. But when the data is displayed in the table the status shows "Showing 1 to 10 of 20" rather than "Showing 1 to 10 of 150". I would highly appreciate if you could let me know if I am missing anything.


Thanks,
busyoonhi

allan

Hi busyoonhi,

I think you might be a little confused about the difference between the server-side processing and ajax sourced file. iTotalDisplayRecords has no effect what so ever when bServerSide:false - which is why you are seeing what you are seeing.

http://datatables.net/examples/data_sources/ajax.html
http://datatables.net/examples/data_sources/server_side.html

Ajax sourced file - A single read of the file, all data loaded and all processing done on client-side
Server-side processing - For every draw of the table (paging, sorting, filtering etc) a call is made to the server to get the data to display.

Which of these are you looking for?

Allan

busyoonhi@yahoo.com

Thanks Allan.
I was wondering if there any option which allows me fetch the data from Server side ( just like pipeline example ) but still allow me to do the pagination and filter on the client side. As you mentioned above, I was trying to use the fnAjaxReload function but then found that the iTotalDisplayRecords value was not honored. Here is what I want to do :

1> Get records from server when the the record is outside the cache ( pipeline ). In this case the JSON will also include the total number of records. For example I may send 100 records out of 1000 total records to the client side.
I want to let user know that whatever is he seeing is out of 1000 total records.
2> Allow user to Paginate/Filter them on the client side using whatever records are received from the Server.

I tried using fnAjaxReload function with bServerSide set to false when the user clicks on the next/previous buttons. But found that the total count is not displayed correctly.

allan

The basic answer is no, it's not possible to use server-side processing for the most part, and client-side filtering on the sub-set. The reason this isn't possible is that DataTables assumes that the full set of data will always be filtered on - what happens if you filter down to two results, but that's only two results out of the first 100 entries. Say 99 on the next 100 entries matched the result?! Wow, that would be confusing.

It is certainly possible to use the pipelining example to load 100 records at a time, and pagination will be done on the client side (until it needs new data), but it's the data set as a whole that DataTables uses - not a sub-set. The fact that 100 records out of 1000 have been loaded doesn't make any difference to the user.

Hope that makes sense!
Allan