Dexie.js: where({}).sortBy('....').offset('...').limit(num).toArray()

[dmlzj]

where({}).sortBy('....').offset('...').limit(num).toArray()
I want to do this,but sortBy return value is promise.
I want to know how to do this.

[dfahlander]

As sortBy is only a shortcut for toArray().then(a => a.sort(...)), you can do it like this instead:

where({}).offset(...).limit(num).toArray().then(a => a.sort((a,b) => a.yourProp < b.yourProp ? -1 : 1);

A future version of Dexie will most likely support orderBy on Collections, but this is the limitation right now.

[dmlzj]

Thank you! But this is not what I want，I want to sort first, and then limit the data number！

[dfahlander]

Such a query might not be optimal even in an SQL database due to the limitation of how btree indexes works. There are different strategies. If your database is large but your limit is low, I would recommend you to do it as one of the following samples (which pretty much mirrors the way an SQL DB would do it):

Sort by query key

In case you want it sorted on the same key that you are querying, it's simple. The result will be sorted by it by default:

db.where('x').between('a','b').limit(l).toArray()

Above query will be sorted by x and limited by l.

Sort by different key

This snipped will:
1) Get all primary keys of the object that matches your query. This is less memory-consuming than getting the results (toArray).
2) Walk the cursor of the sort index and check for primary key matches until the limit is reached.

// Your arbritary query:
const query = db.table.where('x').between(a,b);
// Record all primary keys of the entire result into a Set (hashmap)
const ids = new Set(await query.primaryKeys());
const promises = []; // to collect ids sorted by index;
// Use a sort index to query data:
await db.table
  .orderBy('orderIndex')
  .until(()=>result.length >= limit)
  .eachPrimaryKey(id => {
    if (ids.includes(id)) promises.push(db.table.get(id));
  });
// await the result of retrieving each object by their id:
const result = await Promise.all(promises);

Compound Index Sort

Another more optimized strategy can be used if your query is simple equals-query. You can then declare a compound index [queryProp+sortProp]. This method is the absolutely most optimal as it lets the btree index do the query by itself.

db.version(x).stores({
  table: 'id, [queryProp+sortProp]'
});

Then to do the optimized query, use between instead of equals and you'll get it sorted by sortProp

db.table.where('[queryProp+sortProp]').between(
  [VALUE, Dexie.minKey],
  [VALUE, Dexie.maxKey]).limit(limit).toArray();