Dexie.js: What is the right way to implement pagination?

[Bnaya]

I want to avoid as mush as possible of the work to be inside the storage engine and not memory
So if i understand, collection operations are in memory?

pagination:
sql:

SELECT name from posts ORDER BY id DESC LIMIT 10 OFFSET 10

Dexie:

    db.fbPosts.orderBy('id')
    // in memory ? :(
    .reverse().offset(0).limit(100).toArray()
    .then(addPosts);

[nponiros]

I will assume that you meant sortBy since Dexie doesn't seem to have an orderBy operator.

I would say in order for Dexie to sort all records in a table it would have to read the whole table into memory. From what I see in the code it puts the records into an Array and uses Array.prototype.sort to do the sorting.

There might be operations on a Collection which only use IDBCursor without adding all records into an Array. In that case the browser will define if all the records will be loaded into memory at once or if only parts of the table/store will be in memory at once. I don't really know how browsers handle IDBCursor.

[dfahlander]

Actually, we have Table.prototype.orderBy() that makes the sorting done by the database. Pagination can be done using offset()/limit() as @Bnaya suggested. Same goes for queries like:

db.orders
    .where('[customerId+orderDate]')
    .between(['FooCustomer', Dexie.minKey], ['FooCustomer', Dexie.maxKey])
    .reverse()
    .offset(pageNo * pageSize)
    .limit(pageSize)
    .toArray();

The above query will list orders for a certain customer, ordered by orderDate descending. It assumes you have the compound query [customerId+orderDate] in your schema.

[nponiros]

@dfahlander thanks for the info. I was looking at the Collection instead of the Table..

[Bnaya]

From which line the operations are on the js memory?

[dfahlander]

The entire result will never go into JS memory. Only the limited result. It is using the natural sort order of the index in the database so also in the native implementation of indexedDB, it will not have to read all into memory.

[Bnaya]

Thank you very much for the help, i need to learn more about indexeddb so i will understand better what dexie apis doing under the hood.

[ztsuperman]

Actually, we have Table.prototype.orderBy() that makes the sorting done by the database. Pagination can be done using offset()/limit() as @Bnaya suggested. Same goes for queries like:

db.orders
    .where('[customerId+orderDate]')
    .between(['FooCustomer', Dexie.minKey], ['FooCustomer', Dexie.maxKey])
    .reverse()
    .offset(pageNo * pageSize)
    .limit(pageSize)
    .toArray();

The above query will list orders for a certain customer, ordered by orderDate descending. It assumes you have the compound query [customerId+orderDate] in your schema.
Finally, found this cure.
I think this should be in a demo project. This is a very common use case.