dexie
Dexie multiple sorting
I have a table in Dexie with about ten columns and I would like to get a list of data sorted by multiple indexes and with different orders. Indexes (and sort orders) are dynamically defined by an external form.
In SQL the idea should be something like this:
SELECT * FROM Table ORDER BY name ASC, age DESC, address ASC ect..
Which is the better way to perform that?
IndexedDB can enumerate indexes in ascending or descending order. To include several keys in one index, you can use compound indexes with orderBy but it is not possible to mix direction (having firstName asc, lastName desc for example). See also on how to combine a filter with ordering: https://github.com/dfahlander/Dexie.js/issues/411#issuecomment-268558158
If your sorting requirements goes beyond what is possible using an index, you would need to implement custom sorting on the result. All kinds of databases does this one one or the other way, when indexes falls short to help out with sorting. Dexie has sortBy() but you could equally well do toArray().then(result => result.sort(...sortOptions)) to provide more granual sorting.
When it comes to pagination, it will be easier to do efficiently when you can utilize index for sorting. In the case of custom sorting, pagination can be a little heaver to execute as you cannot start sorting until you have the entire result in memory.
Hello @dfahlander, Any future plan for order by multiple columns?
Example: https://jsstore.net/tutorial/select/order-by/#orderbymultiplecolumn
There are future plans for improving both ordering, querying and paging. Last year the focus has been reactivity and sync. Don't know exactly when Dexie will provide richer queries but maybe in a year or so.
Ordering by multiple columns is already possible if they are index using a compound index. But to combine the ordering with unrelated queries you'd have to do something like this sample.