Sorting and Pagination Related Issues

[xkmy]

Hello, I have a few questions:

When using offset, limit, and sortBy, sortBy only sorts the data after pagination. I have a large orders table and want to sort it by creation time. How can I sort first and then paginate?

Dexie 4.0 has been released. Will it be supported soon? If it won't be supported in the near future, could you explain how to achieve pagination by order creation time using filter according to the official documentation (https://dexie.org/docs/Collection/Collection.offset()#a-better-paging-approach)?

[dfahlander]

The richer queries feature was posponed to 5.0 and we do not have a release date for it so don't expect it to land any time soon.

When dealing with large datasets, use the index for sorting (Either via Table.orderBy() or when requesting further pages, use WhereClause.above() - both of these will order by the given index), then add a .filter() which puts a manual filter on top and then limit() to stop after a desired number of hits.

The pagination approach described in https://dexie.org/docs/Collection/Collection.offset()#a-better-paging-approach is still valid - it uses WhereClause.aboveOrEqual() to resume the previous request from the key it left off, by providing the last visited key to aboveOrEqual(). The reason for not just using above() is that several rows might have the same value for the index we are ordering on, so that would possibly jump over some results. On the other hand aboveOrEqual() will return some same rows again so you need to filter away the results containing a key that was already retrieved in previous page.

This can sound complex but the principle is simple though:

• All queries on an index will result in a sorting of the same index.
• By providing a start key in where('x').aboveOrEqual(k) you achieve the same as orderBy('x') but only return results from key k and forward.
• To get a new page, use the last key from previous page as k, then fast-forward until reaching a primary key not retrieved from the last page.