I'd like to know whether there are more rows available feature combined with paging. Perhaps the easiest way is to skip (pageIndex-1)*pageSize rows and take pageSize+1 rows. The latest would be discarded by UI but it would show whether there are more rows available. Unfortunately paging is limited (cccc). There are two other options I see:

• count the records <- might be slow and certainly lowers perf
• hack the way into llblgen pro sources

Honestly, neither of these are much appealing. Any other solutions?

Executing a Count(*) first will give you the total number of rows, and so you should know before querying for rows (and do paging), how many page should you expect to have, based on the page size.

Walaa wrote:

Executing a Count(*) first will give you the total number of rows, and so you should know before querying for rows (and do paging), how many page should you expect to have, based on the page size.

Sure, but that's two queries - what I try to avoid if possible.

It always gives 2 queries: you have to query the complete set to know the count and the page query will be fetching the page itself, not necessarily the whole set (as that would likely be a lot of rows).

it's done this way to avoid having to query the whole set for the amount of rows again with the second page etc. and it then can optimize the first page fetch by using top without any paging statements.

Otis wrote:

It always gives 2 queries: you have to query the complete set to know the count and the page query will be fetching the page itself, not necessarily the whole set (as that would likely be a lot of rows).

it's done this way to avoid having to query the whole set for the amount of rows again with the second page etc. and it then can optimize the first page fetch by using top without any paging statements.

Ok, but what if I'd like to know only whether there are more rows available (not the typicall pager with page number links). Imagine twitter with dynamic tweets loading (when you scroll down).It knows the page, it knows there are more rows but doesn't care how much records there are because it doesn't matter. Knowing that there are more rows is enough in such cases.

I guess an easy solution would be to display "more rows" always, where the page fetch brings an entire page. And if user clicks on "more rows" button it simply displays "no more rows" if there are no more.

nice and easy

Couldn't you try to retrieve 1 more row than you are going to display?

Say your display page size is 20, try to retrieve 21, if you got 21 back you display more records available, if you get back 20 or less you don't.

arschr wrote:

Couldn't you try to retrieve 1 more row than you are going to display?

Say your display page size is 20, try to retrieve 21, if you got 21 back you display more records available, if you get back 20 or less you don't.

Well, yes, that's exactly my initial attempt. However LLBLGenPro does retrieve only pagesize and can't fetch pagesize+1 due to the internals...

You could fetch with twice the page size than you display. This fetches some more rows but you could do the 'more rows' based on the # of rows you fetch.

Otis wrote:

You could fetch with twice the page size than you display. This fetches some more rows but you could do the 'more rows' based on the # of rows you fetch.

That would work for odd pages, but not for even ones. Or would it?

I mean when I have to collect the first one I'd go with (index=1, size=twice) that would fetch me the first and the second page - good enough there. But when I need second page I'd face problems. I'd have to fetch index=1 and size=three times. Something like that. Still feasible with a bit or arithmetic and fetching more data.

Well, yes, that's exactly my initial attempt. However LLBLGenPro does retrieve only pagesize and can't fetch pagesize+1 due to the internals...

I guess for this to work with the current page number/page size system you would have to maintain a rolling buffer to display out-of.

May be a case for the Skip/Take system to be added.

That's easy IMHO.

Your page size is x You always skip multiples of x But you take x+1, and you only display x.

What do you think?

Walaa, I think that won't work as the skip value has to be a multiple of take as we have a pagesize/number paging system, not a skip / take paging system

It was indeed my idea as well more or less, but indeed, it won't work in all cases.

The current system is a bit problematic, as you can't skip n and take m rows if n isn't a multiple of m, as the api expects a page size and a page number. To add that we need yet more parameters and overloads, as everything has to stay backwards compatible.

In this case, it's not a big problem though: - fetch the total count, which is N - then simply page through the set with page size S. - you display 'more rows available' if N > (S * pagenumber).

Now, the argument that there might be more rows added or rows removed during the paging action is also true for whether you pull the count each time: Say that's the case, you get the count each time you fetch a page: you get the last page and the count is (pagesize*pagenumber). However when the page is displayed, someone adds a row. Then it's not valid too.

I've looked into adding skipping into the framework without adding any overloads, but it always felt like a bit of a hack, e.g. use the maxNumberOfElementsToReturn as the take value and pagenumber as the # to skip, and go into this mode if pagesize is 0. (or something like that). Though as we now have two modern query api's, it might be time to add this, or add yet another overload which accepts a single object with parameters.

We might have to do that anyway with new features added to v4

File it "would be nice to have" but "definitely not a priority" I guess.

mihies wrote:

File it "would be nice to have" but "definitely not a priority" I guess.

Well, in v4 we need to pass more query info to the api anyway (from linq and queryspec), if we manage to squeeze in level 1 caching and hints into the api, so we need the overload anyway. The DQE's can handle it, it's 1 if-then-else , it's just how to get the info to the DQEs without a lot of problems.

Besides, I always hated our skip/take paging system since it dawned to me that paging with 'skip x, take n' is actually more flexible, however less convenient. (yes, I admit I sometimes are that daft )