server side pagination and client side sorting and filtering.

server side pagination and client side sorting and filtering.

panojkumarpanojkumar Posts: 5Questions: 1Answers: 0

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.

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • panojkumarpanojkumar Posts: 5Questions: 1Answers: 0
    edited October 2021

    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.

  • kthorngrenkthorngren Posts: 20,140Questions: 26Answers: 4,735

    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

  • panojkumarpanojkumar Posts: 5Questions: 1Answers: 0
    edited October 2021

    @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.

  • kthorngrenkthorngren Posts: 20,140Questions: 26Answers: 4,735

    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

  • panojkumarpanojkumar Posts: 5Questions: 1Answers: 0

    @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.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    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

  • kthorngrenkthorngren Posts: 20,140Questions: 26Answers: 4,735

    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

  • panojkumarpanojkumar Posts: 5Questions: 1Answers: 0
    edited October 2021

    @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;
            }
    
Sign In or Register to comment.