Server-side processing with unknown iTotalRecords count

datatables

Does datatables have any support for server-side retrieving of data where the iTotalRecordscount is not known in advance?

We have data for which the query to get the total-count is almost as expensive as just querying all records. It would be need if there was a way to let datatables know that the iTotalRecords count is not known. Such functionality isn't documented. Is it supported?

allan

This is quite an interesting one - the basic answer is no - it's not possible because DataTables uses the record count that is passed back to it to deal with the paging controls (i.e. should they be enabled or not) and also the information element. So the display records are needed, but it would be possible to just pass back a static number (like 1'000'000 or whatever) which would make DataTables think there are a million rows. You could hide the information element if this information is totally bogus!

Regards,
Allan

datatables

I ended up doing something like that, and using the fnFormatNumber callback to rewrite the special number into "an unknown number of", which works quite will in combination with the simple pagination.

andyburke

I'd like to second this. It would be great if we could pass back a value that indicates we don't know the total records (same for iTotalDisplayRecords).

allan

Its certainly possible - although how will the server-side script cope with paging when DataTables asks you to page past the current record set? Or enabling and disabling paging controls when you reach the limits of the record set (or have a really small record set which would fit onto one page).

Allan

andyburke

Maybe we could send back a special value like -1 in displayrecords to indicate that we are at the end?

allan

Yup I think a special value would certainly be needed. I'm not sure what scenario would lead to knowing that you are at the end of the data, but at the same time not knowing how many records are in the dataset though? Do you have a scenario you were thinking of?

Allan

andyburke

Sure. We're using Google AppEngine's datastore, which has no inexpensive way to know the number of rows. However, when we try to fetch rows and get back fewer than our limit, we know we've reached the end.

allan

Interesting - thanks for that (I'm thinking I should get to grips with AppEngine at some point!). I'm still a little unsure about the interaction when you page 'over the end' - should it flash up saying 'no more records' or something. Another option might be to get a get on the number of rows just once per loaded page (or perhaps a cron job which does it once every 15 minutes for all sessions) that can be stored somewhere and then returned.

I'll have a think about how it might be implemented in future.

Regards,
Allan

andyburke

With the App Engine datastore, query expense is related to rows returned. Finding a count of rows is almost as expensive as getting all those rows, as well. So, if you have a table with many, many rows it can be prohibitively expensive to get a count.

I think the point would be to return a sentinel value like -1 (or to add a field like bMoreRecords) and then have the table disable the next page button. So, let's say they've paged in a ways, they click 'next page' again and instead of the 10 records requested, we can only get back 7 (we've reached the end of the table). We could then return a sentinel value or set bMoreRecords to false in our response. When DataTables sees that, it can disable the next page button.

That would basically be the 'bigtable way' of handling this.

Oly

+1 I would love this functionality. Counting rows is always a performance danger and should be avoided where possible. Would be great to have some supporting functionality to make it easy to visualise huge datasets. In my scenario I am displaying a huge event log - not only is the number of rows too big to count but it is constantly increasing - so each 'next page' request may or may not be the last. Searching for stuff in your Gmail account is a pretty good example of how to do this sort of stuff and you get messages along the botton of the table saying things like:

" 1 - 20 of tens of thousands Older ›"