iTotalRecords retrieved by AJAX

iTotalRecords retrieved by AJAX

nuhusky001nuhusky001 Posts: 4Questions: 0Answers: 0
edited January 2014 in DataTables 1.9
I'm using infinite scrolling and server side processing. How do I retrieve the iTotalRecords after I've already displayed the first set of results. Meaning, if I have 500 results and I'm display the first 125, I want iTotalRecords to be updated after I've already retrieved the first 125. Why do I want to do this? Getting the SQL_CALC_ROWS_FOUND is much more expensive then getting first 125 records. So, I can do that with a second request and update iTotalRecords after displaying the records to my customers.

Thanks

Replies

  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    > How do I retrieve the iTotalRecords after I've already displayed the first set of results

    You don't - iTotalRecords is required up front in DataTables server-side processing mode. Sorry.

    Allan
  • nuhusky001nuhusky001 Posts: 4Questions: 0Answers: 0
    Is this planned at all for a future feature? Some of my queries are very large with multiple table joins. Getting the total count is expensive to do up front. Could I just set it to some large number and then set it to the real number after the second request?

    would this work?
    https://github.com/DataTables/DataTables/pull/60/files
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Possibly - I haven't tried merging that into DataTables yet, and I'm not sure of its impact upon a number of aspects of DataTables, such as paging. Currently I'm not planning to add this feature in, but I might revise that in future, once I've got the features that I do plan to add in, these there is already a huge backlog! :-)

    Allan
  • nuhusky001nuhusky001 Posts: 4Questions: 0Answers: 0
    Great. I noticed you do not accept donations any longer. Whats the best way to donate?

    About the situation, I will try to do a few things to make this work. I've already got it half way working as I don't have to worry about filtering since its all server side processing. Right now I hardcode Total = 1000 if the result set is under the page #. In this case 125. If its over 125, I set to 1000. Seems to be working.. now I need to add in the AJAX call to get the real result after the page is displayed. I will show a spinning graphic in the mean time. I will let you know if this works, for all others also.
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    > Great. I noticed you do not accept donations any longer. Whats the best way to donate?

    Thanks for asking :-). I'm going to be added a purchase option soon, which is effectively the same thing, but will keep my accounts sane :-).

    That does sound like it would work - interested to hear how you get on with it as it certainly would be a good thing to add into the core.

    Allan
  • nuhusky001nuhusky001 Posts: 4Questions: 0Answers: 0
    So I got it working with infinity scrolling. A few issues we did run into...

    1) You can't have paging on... meaning if you have 25 results per page and have 5 pages turned on in infinity scrolling, you have to switch this to 1 page and 125 results per page.
    2) We set iTotalRecords to some unique number, 123456789 and set that as the "trigger" for the AJAX to call and query the DB for the total.
    3) ALL filtering must be server side. I do not have any client side filtering turned on or sorting. All requests are server side.
    4) Once we get the iTotalRecords we pass it along in the POST request for the next 125 fetch, and so we don't have to hammer the DB a second time for the total... we already have it. And since all filtering is server side, we don't have to worry about this number changing on the client.
    5) If the results are less than the 125 we just manually set iTotalRecords to the aaData size (before we send it off to the client) since we know that if less than 125 then we already have iTotalRecords and no need to launch AJAX call.

    This works for our application, but I can see issues where there is a lot of client side things happening which do not lend themselves well to this method. Example: A typical long query now shows the results in 2 seconds... the Total # displayed to the customer is a spinning loading graphic until we get the AJAX response, it takes the DB 7-8 seconds to get total rows (this is a multiple table join), but the customer sees results in under 2. This is a much better experience for customers. They want to see results ASAP. Once we get the result from the AJAX call, we remove the spinning loading graphic with the iTotalRecords and we update the iTotalRecordsAJAX variable in the POST statement for the next 125.
  • allanallan Posts: 63,498Questions: 1Answers: 10,471 Site admin
    Very interesting - thanks for your feedback on this!

    Good to hear that you've got it working as well :-)

    Allan
This discussion has been closed.