Dexie.js: Query on multiple multi-entry indexes doesn't seem to work

[spingary]

I have a table with 2 multi-entry indexes. When I try to query on both of them, it doesn't seem to work:

var db = new Dexie("MyFriendDB");
db.version(1).stores({
    friends: '++id,name,age,*categories,*tags'
});
db.open().then(function(){
    return db.friends.bulkAdd([
  {name: "Foo", age: 42, categories: ['cat_a','cat_b'], tags: ['tag_a','tag_b']},
  {name: "Bar", age: 32, categories: ['cat_b','cat_c'], tags: ['tag_b','tag_c']}
  ]); 
})

This works fine:

    .where({'categories':'cat_a'})

But this returns nothing but I expect it to return the same row:

    .where({'categories':'cat_a','tags':'tag_a'})

https://fiddle.jshell.net/jquey2fj/1/

Is this not supported? Or is this when I should start breaking the data up into multiple tables and use join logic?

I tried using Dexie 2.0.2 and Dexie 3.0.0.alpha on both late model Chrome and Safari.

Thank you.
Gary

[dfahlander]

When multiple queries are specified and there is no compound index containing both keys, dexie will utilize only the first index and manually filter on the second. This is where it goes wrong as the manual filtering is done using indexedDB.cmp(obj.tags, 'tag_a') === 0 rather than obj.tags.some(tag => indexedDB.cmp(tag, 'tag_a') === 0). So it's a bug that can be easily fixed in dexie.

Workaround so far:

db.friends.where({'categories':'cat_a'})
  .filter(f => f.tags.some(tag => tag === 'tag_a')
  .toArray();

This workaround would be as performant as the bugfix will be.

That said, you could also gain better performance by concattenating categories and tags in a single multiEntry field. This can though result in an exploding length of that array if both sets are very big.

function flatten (a) {
    return [].concat.apply([], a);
}

friend.tagsCats = flatten(
  friend.tags.map(tag =>[tag].concat(
    friend.categories.map(cat =>
      tag + ':' + cat)))
);

DB facing code would then be:

// Schema:
db.version(1).stores({
    friends: '++id,name,age,*tagsCats'
});

// Query
db.friends.where({
   tagsCats: tag + ":" + category
})

What this essentially does is emulating a compound index of two multiEntry indexes. The upside is super fast AND criteria query between two multiEntry fields. The downside can be the size of the tagsCats array.

[spingary]

Thank you for the tips @dfahlander! This is very helpful. I like the concatenating categories solution as well, but I chose the filter route to keep things simple. Fantastic!