Write multiple where condition using Dexie.js

Question

How can I write a where condition like the following using Dexie.js?

field1=0 AND field2!='test' AND field3 IN(1,2,3,4)

I tried this one but I got an error....

.where('field1').equals(0).and('field2').notEquals('test').and('field3').anyOf([1,2,3,4]).toArray()

I suppose because the ".and" method works only with a function. But if I have to write a multiple where condition with different types of conditions (e.g.: equals, notEquals, anyOf)... How can I do that?

I apreciate your support, thanks in advance

Stronghold

Answer 1

Collection.and() is identical to Collection.filter() and takes a JS callback.

The reason is that an index can only be used for one criteria. A typical SQL database has the same limitation but it has some intelligent strategies ( query plan ) that tries to find out the which field to use for index lookup and which ones to use for manually filtering the result. Dexie does not have query plans so you need to do that logic yourself.

For cases where you are combining an equals filter with another filter, a compound index can be used also to get more efficient AND queries.

So as an example we can take your exact example and translate it into the most optimal way using dexie queries:

const db = new Dexie("multipleCriteriasSample");
db.version(1).stores({
  someTable: 'id, [field1+field3]'
});

function doTheQuery() {
  return db.someTable.where('[field1+field3]').anyOf([
    [0, 1],
    [0, 2],
    [0, 3],
    [0, 4]
  ]).and(item => item.field2 !== "test");
}

The logic for the above is this:

field1 is an equals operator - which makes it possible to combine with another criteria if we have it in a compound index ('[field1+field3]') field2 is a notEqual which generally never has any gain of using an index - so filter it using the and() method. field3 is an anyOf(). Use the compound index and do a single anyOf().

Stronghold

So long with the "easy to use" promise. >> Dexie does not have query plans so you need to do that logic yourself. – Dendritic