Equivalent of where clause in indexeddb

Question

Let's assume we got a database for todolist and want to query all item that are important and are not already done. In SQL, I will use something like

SELECT * FROM todolist WHERE important = true AND state <> 'done'

How can we perform that type of request in an indexeddb nosql database? With indexes? Another way? Not possible?

As I know to filter result on important = true :

objectstore.index('important').openCursor(IDBKeyRange.only('true'))

But I do not know how to filter on state <> 'done' as we got only IDBKeyRange.only(z).

And I do not know how to filter on both clause.

N.B. : In MongoDB we do :

db.userdetails.find({"date_of_join" : "16/10/2010","education":"M.C.A."})

possible duplicate of [In IndexedDB, is there a way to make a sorted compound query?](http://stackoverflow.com/questions/12084177/in-indexeddb-is-there-a-way-to-make-a-sorted-compound-query) — Josh, May 22 '14 at 19:58
Checkout this library [JsStore](http://jsstore.net/tutorial/where) — Uday, Sep 05 '17 at 07:38
If you want to use sql query for indexeddb, check out sqlweb - http://jsstore.net/tutorial/sqlweb/ — Ujjwal Kumar Gupta, Dec 02 '18 at 05:16

score 6 · Answer 1 · answered Mar 25 '13 at 21:58

6

In onupgradeneeded, create an index on the criteria that uses an array:

todolistStore.createIndex('importantIncomplete', ['important','state'],{unique:false});

For your query do:

var lowerBound = ['true','started'];
var upperBound = ['true','almostdone'];
var range = IDBKeyRange.bound(lowerBound,upperBound);
var request = todolistStore.index('importantIncomplete').openCursor(range);

answered Mar 25 '13 at 21:58

Josh

17,834
7
50
68

I'm not sure what this upper and lower bound are supposed to be... for example, what if you want to search for not in the "blocked" state? – Michael Sep 06 '21 at 22:45

score 1 · Answer 2 · answered Mar 26 '13 at 05:05

There are two ways to query multiple indexes in IndexedDB. Josh described the fastest way of querying multiple fields using composite index. However it has cost of storage and slow down writing. Additionally query must be known a priori and hence index are created as required. Second method is manual key joining using sorted merge or others algorithm. This method requires indexes (not composite) on fields of interested and work all combination query. See here http://dev.yathit.com/ydn-db/nosql-query.html for sorted merge, nested loop and zigzag merge implementation in ydn-db library. I attend to add more key joining algorithms.

BTW, generally we don't index boolean value. If your query field is boolean data type, just do table scan. Not point in using index.

score -5 · Accepted Answer · edited Nov 23 '12 at 08:37

-5

You will need indexes for that, and you can retrieve the data using a cursor where you can provide one filter.

On my blog (http://www.kristofdegrave.be/2012/01/indexed-db-reading-multiple-records.html?m=1) you can find some more info about it.

edited Nov 23 '12 at 08:37

enguerran

3,193
3
26
42

answered Nov 22 '12 at 18:55

Kristof Degrave

4,142
22
32

I need more details to success with your answer. I can filter result with `objectstore.index('important').openCursor(IDBKeyRange.only('true'))` which filter only on `important = true`. But I do not know how to filter on `state <> 'done'` as we got only `IDBKeyRange.only(z)`. And so I do not know how to filter on both clause. – enguerran Nov 23 '12 at 08:41
If you want to filter on multiple properties, the best way to do this, is retrieve all records from the object store and in the cursor method you keep an array with the results in where you only add the objects that fit the given condition. If you hate doing this all on your own, you can maybe look for a library that allready implements these possibilities. I've written one called linq2indexeddb (http://linq2indexeddb.codeplex.com). – Kristof Degrave Nov 23 '12 at 08:47
Ok, that was my other idea : retrieve everything then filter with a 'if' clause. And I would look at solution with linq. Thank you. – enguerran Nov 23 '12 at 08:50
3

Your link is broken, which is why it's best to put the actual answer as opposed to a link. – Kenton de Jong Mar 21 '19 at 20:50

Equivalent of where clause in indexeddb

3 Answers3