server side pagination and client side sorting and filtering.

panojkumar

Hi Team

I know this a old question but was wondering if it was implemented?

we could simply add serverside processing option on column and filter level to achieve this.

I know if the concept of client side filtering/sorting does not make sense with server side pagination but still if Datatable provide that flexibility its no harm.

Please advise.

colin

No, it's still the case that if serverSide is enabled, then the server is responsible for all sorting, filtering and paging. This has to be the case as the client only has the data available on that page,

Colin

panojkumar

yes, I agree. I understand what you are saying. I said the same thing to my management but they argued and it makes sense to not provide server side processing for sorting as the result is huge and user is not getting any meaningful result.

I am dealing with IOT data whihc is very huge. Hundreds of millions of records.

Also we are using synapse - a parallel query processing DB---the buisness model is it charges for EVERY QUERY. We need to minimize number of queries too.

And Even if the users sort, he wont get meaning results as millions of records. Thats is why sort is really not good idea in our case.

But if you provide this functionality its really helpful. Think about what is the harm in providing it. Its just another flexibility. Let the user decide if it makes sense or not to his users.

kthorngren

One option is to disable server side processing and use ajax.data to send paging, sorting and search parameters to the server. Update your server script to read these parameters and return the dataset. Then you can use client side processing of the table data.

See this example. Even though the example is using server side processing you don't have to for the ajax.data.

Kevin

panojkumar

@kthorngren I tried doing that but the response that I need to provide to the datatable is just the list of data not like the one in server side processing.

so server side processing gives this response

result = {
Draw = 0,
RecordsTotal = 20000
RecordsFiltered = 20000,
Data = dataset
}

but client side processing only accepsts
results = {Data = dataset} the above will not work.

So I am suppose to tell datatable there are actually 20000 records?

when I do client side processing, and get data thru ajax in the above format, the datatable does not recognise it. It only recognises if do "result.Data". When I do that, Pagination is gone, what I mean is, suppose the result.Data has 10 records, it only show 1 page. It does not show pages for 20000 records i.,e
1, 2, 3....2000 . So pagination is gone

So how should i update the datatable informing that there are actually 20000 records. And the user can actually navigate to other pages.

kthorngren

when I do client side processing, and get data thru ajax in the above format, the datatable does not recognise it.

Sounds like you need to use ajax.dataSrc to point Datatables to the location of the data. Guessing you would use something like the second example in the docs.

It only recognises if do "result.Data". When I do that, Pagination is gone, what I mean is, suppose the result.Data has 10 records, it only show 1 page. It does not show pages for 20000 records i.,e

If you are only returning 10 rows of data, with client side processing, then that is all Datatables knows about. Client side processing assumes all the data is sent to the client.

If you want a hybrid approach with sorting and searching client side but server side paging then you will need to write some custom client side code to handle the paging and the server side code will need to send the row count for the custom client code to use. Or you can modify the datatables.js server side code to meet your needs. It is open source code.

Its been asked before to have client side searching and sorting with server side processing. I don't want to speak for the developers but I don't think there are any plans for this type of change.

Another option to reduce server side processing ajax calls is to use Pipelining.

Kevin

panojkumar

@colin

yes, I agree. I understand what you are saying. I said the same thing to my management but they argued and it makes sense to not provide server side processing for sorting as the result is huge and user is not getting any meaningful result.

I am dealing with IOT data whihc is very huge. Hundreds of millions of records.

Also we are using synapse - a parallel query processing DB---the buisness model is it charges for EVERY QUERY. We need to minimize number of queries too.

And Even if the users sort, he wont get meaning results as millions of records. Thats is why sort is really not good idea in our case.

But if you provide this functionality its really helpful. Think about what is the harm in providing it. Its just another flexibility. Let the user decide if it makes sense or not to his users.

colin

And Even if the users sort, he wont get meaning results as millions of records. Thats is why sort is really not good idea in our case.

That's the key, there's no value to client-side sort when the data is server-side, as you'd only be sorting what's on the page, which the server should be doing anyway.

It's not a thing that's on our roadmap, I'm afraid, but as it's open-source, if it's important to your management they could make those changes.

Colin

kthorngren

My suggestion above was not clearly explained. Use client side processing and return the 20000 or however many rows you want at the client to be able to search and sort. Then create your own input to allow the user to fetch a new data set. Fetch the new dataset and either append it to the current table data or overwrite the current table data.

Kevin

panojkumar

@colin @kthorngren

I implemented client side processing with server side pagination using Pipelining code. Just comment out the below lines in the pipelining code and write custom sorting/searching logic.

Basically during pipelining, dont invalidate the cache when there is change in order/search and thats it.

It doesnot have to be pipelining code but that sample is available online.

else if (JSON.stringify(request.order) !== JSON.stringify(cacheLastRequest.order) ||
            JSON.stringify(request.columns) !== JSON.stringify(cacheLastRequest.columns) ||
            JSON.stringify(request.search) !== JSON.stringify(cacheLastRequest.search)
        ) {
            // properties changed (ordering, columns, searching)
            ajax = true;
        }