dfahlander
commented
8 years ago Either use compound indexes, or use Collection.and().
If you can live with only targeting Chrome, Firefox or Opera, you can use compound indexes. If it must work on Safari, IndexedDBShim, Edge or IE, you cannot use compound indexes today. There's a shim that enables it for IE/Edge though, but it is still in beta, so I would recommend to instead use Collection.and() for those cases.
Let' say you have a form where users can fill in various attributes of friends:
<form>
<input name="name"/>
<input name="age"/>
<input name="shoeSize" />
</form>Using Collection.and()
First, pick the most probably index to start your search on. In this case, "name" would be a perfect index that wouldn't match so many items, while age or shoeSize would probably match more friends.
Schema:
db.version(X).stores({
friends: "id, name, age, shoeSize"
});Query:
function prepareQuery () {
// Pick a good index. The picked index will filter out with IndexedDB's built-in keyrange
var query;
if (form.name.value) {
query = db.friends.where('name').equals(form.name.value);
} else if (form.age.value) {
query = db.friends.where('age').equals(parseInt(form.age.value));
} else if (form.shoeSize.value) {
query = db.friends.where('shoeSize').equals(parseInt(form.shoeSize.value));
} else {
query = db.friends.toCollection();
}
// Then manually filter the result. May filter a field that the DB has already filtered out,
// but the time that takes is negligible.
return query.and (function (friend) {
return (
(!form.name.value || friend.name === form.name.value) &&
(!form.age.value || friend.age == form.age.value) &&
(!form.shoeSize.value || friend.shoeSize == form.shoeSize.value));
});
}
// Run the query:
form.onsubmit = function () {
prepareQuery() // Returns a Collection
.limit(25) // Optionally add a limit onto the Collection
.toArray(function (result) { // Execute query
alert (JSON.stringify(result, null, 4));
})
.catch (function (e) {
alert ("Oops: " + e);
});
}
Using compound indexes
As written above, compound indexes code will only work on mozilla- and chromium based browsers.
db.version(x).stores({
friends: "id, name, age, shoeSize," +
"[name+age+shoeSize]," +
"[name+shoeSize]," +
"[name+age]," +
"[age+shoeSize]"
});The prepareQuery() function when using compound indexes:
function prepareQuery() {
var indexes = []; // Array of Array[index, key]
if (form.name.value)
indexes.push(["name", form.name.value]);
if (form.age.value)
indexes.push(["age", parseInt(form.age.value)]);
if (form.shoeSize.value)
indexes.push(["shoeSize", parseInt(form.shoeSize.value)]);
var index = indexes.map(x => x[0]).join('+'),
keys = indexes.map(x => x[1]);
if (indexes.length === 0) {
// No field filled in. Return unfiltered Collection
return db.friends.toCollection();
} else if (indexes.length === 1) {
// Single field filled in. Use simple index:
return db.friends.where(index).equals(keys[0]);
} else {
// Multiple fields filled in. Use compound index:
return db.friends.where("[" + index + "]").equals(keys);
}
}
// Run the query:
form.onsubmit = function () {
prepareQuery() // Returns a Collection
.limit(25) // Optionally add a limit onto the Collection
.toArray(function (result) { // Execute query
alert (JSON.stringify(result, null, 4));
})
.catch (function (e) {
alert ("Oops: " + e);
});
}Using arrow functions here to make it more readable. Also, you're targeting chromium or firefox and they support it already.
JeffB1
trihicham6
Just after advice as to best way to handle this as I didn't really see anything in the tuts.
I have a search screen where the user can enter various criteria. They may enter all or some of the criteria. I need to build the where clause for this but don't really know how to do it. I had looked at chaining promises together so I could do a where on the first criteria and get a set of results, then on that result do another where and so on until I had processed all criteria. However, the first where returns a collection and there where seems to only work on a table. Any suggestions?
Thanks
Jeff