Is Infinite Scroll possible without a recordsTotal?

ipwright

We've got an infinite scrolling tables, not too dissimilar to http://legacy.datatables.net/release-datatables/examples/basic_init/scroll_y_infinite.html. One of the things we've noticed in our back-end is that it's quite expensive to get the total row count used by the recordsTotal in data tables.

I'm wondering if anyone has come across a way to run the infinite scroll without a total count?

We've tried a few things to no avail - If we take it out we don't seem to get any paging, if we fake it to a really large number (when there aren't really the rows to support it) it gives a nasty experience.

colin

Hi @ipwright,

If you mean the "Showing 1 to 57 of 57 entries" line, then that can be hidden with the dom. If you omit the i option, such as lfrtp then it's removed, as in this example here.

Cheers,

Colin

ipwright

Hi @colin

No it isn't actually the part of the DOM. The actual problem we have is due to our database technology we're using, retrieving a total row count for a large number of rows is a very expensive operation.

We're obviously investigating if we can speed that up, but at the moment the only reason we need to provide a total row count is for the infinite scroll within DataTables. I'm wondering if there's something we can do at the table level to still allow infinite scrolling, but to run till no data is returned rather than using a hard limit via the recordsTotal.

Thanks

colin

Hi @ipwright ,

You must have the slowest database ever if a count runs slowly

It might be worth creating a live example, or linking to your page, as I'm not clear how you're getting the data. If you're doing it with Ajax, you could probably do a hack that might work.

Cheers,

Colin

allan

Unfortunately, at the moment there is no option to not supply the number of records in the table at the moment. This is something we are considering how to add for future releases.

Are you using Mongo or some other NoSQL db? Often their table row count can be fairly slow.

Allan

ipwright

@colin seems to be a problem with the database technology we're using. It does a whole load of stuff very well, but a count it struggles to optimize.

It's a bit difficult to put together an example at the moment but yeah we're using async json requests to load the data. So essentially:

1) Request to go get first page of data (take=50, skip=0)
2) Table added to the page with this data
3) Scroll down on the table
4) Request to go get the second page of data (take=50, skip=50)

Basically the crux of it is the return response:

{
    data: [1, 2, 3, ...],
    recordsTotal: 100, // We want to omit this param
    recordsFiltered: 100, // And also this one
};

ipwright

@allan sorry I only just saw this response.

Thanks for the clarification. I'm assuming this is something that would be possible if we wrote our own DataTables plugin? Or would internal assumptions within DataTables prevent us from achieving that?

On the database question, yes we're using Neo4J which is a graph based database. It's unable to optimize a statement and has to obtain the full dataset to grab the count out, which is incredibly slow

allan

Or would internal assumptions within DataTables prevent us from achieving that?

Correct. At least for the moment .

Does your database give you a flag to indicate if there is more data available that what it returns in the current row? That is the major issue that we face with DataTables - we use the recordsFiltered property to determine what paging control buttons should be active.

Allan

ipwright

No, unfortunately there's no way to know if there's more data available unless you got less rows back then requested - which obviously only happens on the last page.

allan

Ouch - so you could be on the last page of a table that just happens to have a number of rows which is divisible by the page length, click on "next" and then get nothing.

I guess if you are using server-side processing at all, then you are unlikely to click "next" through 50k+ records, so it might not be too common. It does mean that something like Scroller would be impossible though since that has to know the number of records.

Allan

ipwright

@allan yeah that's the big problem. We don't use the "paged" approach so page numbers (and even the count) aren't really an issue. But as you say the infinite scroller needs to know the number of records.

I guess from an implementation point of view what I'd expect is if the scroll bar appeared with some "buffer", and as you scroll into that buffer area new data would obviously be loaded via the paged requests, and the scrollbar height re-calculated to represent the larger dataset while maintaining that buffer. Then as soon as you get less than the pageSize of rows back you only partially scroll the table to the end of the content and hit the bottom of the scrollbar.

All easier said than done I know!

colin

Hi @ipwright ,

Here's one idea, bit of a hack but I think it should work, Allan would be able to confirm.

As you said before, the problem is

{
    data: [1, 2, 3, ...],
    recordsTotal: 100, // We want to omit this param
    recordsFiltered: 100, // And also this one
};

You can send custom variables in the Ajax request, see this example here. And you can also pre-process the Ajax response, see here.

Sooooo, what you could do is send a flag in the request, which determines whether you want the server to calculate the size or not - this would only be set on the very first request, as you need to get the size at least once; all other times, the response would just return 0 for those fields.

Then, on the pre-processing, on the first response, you store the length locally on the client, and with the subsequent responses you slot in that stored value that was returned before.

It should mean you only need to request the length just the one time. Hope that makes sense and is possible,

Cheers,

Colin

ipwright

Hi @colin, thanks for the suggestion.

We've already considered something similar, we have an interim API which we could quite easily apply some caching to (so no need to hack data tables) to achieve the same effect. (Great minds think alike!).

I think we've run the full course with this thread though, until data tables supports not having the recordsTotal. @allan it would be interesting (if you know) where this sits on any roadmap you guys have, if indeed it has made it into a priority list.

allan

I guess from an implementation point of view what I'd expect is if the scroll bar appeared with some "buffer", and as you scroll into that buffer area new data would obviously be loaded via the paged requests, and the scrollbar height re-calculated to represent the larger dataset while maintaining that buffer.

That's actually doable at the moment - rather than using DataTables own Ajax methods to load the data, just run it in client-side processing mode and use rows.add() to add the new data when you hit some kind of point where you need to load more data.

While I want to add support for not returning recordsTotal, I think it would have to be supported with a paging approach only. Scrolling just wouldn't work.

Allan