iTotalRecords retrieved by AJAX
iTotalRecords retrieved by AJAX
nuhusky001
Posts: 4Questions: 0Answers: 0
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
Thanks
This discussion has been closed.
Replies
You don't - iTotalRecords is required up front in DataTables server-side processing mode. Sorry.
Allan
would this work?
https://github.com/DataTables/DataTables/pull/60/files
Allan
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.
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
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.
Good to hear that you've got it working as well :-)
Allan