Query using multiple conditions [duplicate]

Question

I recently discovered (sadly) that WebSQL is no longer being supported for HTML5 and that IndexedDB will be replacing it instead.

I'm wondering if there is any way to query or search through the entries of an IndexedDB in a similar way to how I can use SQL to search for an entry satisfying multiple conditions.

I've seen that I can search through IndexedDB using one condition with the KeyRange. However, I can't seem to find any way to search two or more columns of data without grabbing all the data from the database and doing it with for loops.

I know this is a new feature that's barely implemented in the browsers, but I have a project that I'm starting and I'm researching the different ways I could do it.

Thank you!

Benitabenites

P.S. I tried this using LocalStorage and storing a JSON encoded array and using for loops to sort through my conditions. However, my table is 4000+ entries and looping through all of them takes more than (probably) 100 milliseconds, which is way too long. If I used IndexedDB, I don't see how I can speed this up unless I can do multi-column queries. – Benitabenites

In brief: you need to create an index on the properties you want query, then you grab the index in a transaction and then call the openCursor method on that. I can come up with an example but it may take me a day or so... – Beeson

Thanks for the response. I've tried to do that, but I could not find out how to query a multirow index to do something like a multiple column comparison in the WHERE statement of SQL. Do you have any advice for this? – Benitabenites

I'll have a go at working out an example in the next couple of days and post a proper answer. – Beeson

@Benitabenites Not sure how you are getting such low times. I just tried to find elements containing a sequence of characters. The set contained 25,000+ elements. The time it took to filter the set only took anywhere between 3-7ms. My guess is, you are loading from local storage immediately before searching. That is bad considering when you load from LocalStorage you have to parse JSON every time (assuming you are storing as json). – Counterpane

@Counterpane Is that for IndexedDB or for LocalStorage json? I would imagine for either case that you're getting better benchmarks due to engine and processing improvements (my numbers are 6 years old). For LocalStorage numbers, I don't remember if I took into account JSON parsing time, probably did. – Benitabenites

Answer 1

Check out this answer to the same question. It is more detailed than the answer I give here. The keypath parameter to store.createIndex and IDBKeyRange methods can be an array. So, crude example:

// In onupgradeneeded
var store = db.createObjectStore('mystore');
store.createIndex('myindex', ['prop1','prop2'], {unique:false});

// In your query section
var transaction = db.transaction('mystore','readonly');
var store = transaction.objectStore('mystore');
var index = store.index('myindex');
// Select only those records where prop1=value1 and prop2=value2
var request = index.openCursor(IDBKeyRange.only([value1, value2]));
// Select the first matching record
var request = index.get(IDBKeyRange.only([value1, value2]));

Canada

there is a second parameter to the options of the createIndex method: multiEntry which tells the indexedDB if the index should be created on each one of the elements in the array or on the array as a whole. More information here: w3.org/TR/IndexedDB/… – Wb

Use " index.getAll( [value1, value2] ) " Take care: value1 and 2 must not be undefined as you will get the error - The parameter is not a valid key. – Amends

If someone is checking @MichaRoon's link, you have to scroll less with this anchor: w3.org/TR/IndexedDB/#index-construct – Unclean

Answer 2

Let's say your SQL Query is something like:

SELECT * FROM TableName WHERE Column1 = 'value1' AND Column2 = 'value2'

Equivalent Query in JsStore library:

var Connection = new JsStore.Instance("YourDbName");
Connection.select({
    From: "YourTableName"
    Where: {
        Column1: 'value1',
        Column2: 'value2'
    },
    OnSuccess:function (results){
        console.log(results);
    },
    OnError:function (error) {
        console.log(error);
    }
});

Now, if you are wondering what JsStore is, let me tell you it is a library to query IndexedDB in a simplified manner. Click here to learn more about JsStore

Matutinal

Answer 3

I mention some suggestions for querying relationships in my answer to this question, which may be of interest:

Conceptual problems with IndexedDB (relationships etc.)

As to querying multiple fields at once, it doesn't look like there's a native way to do that in IndexedDB (I could be wrong; I'm still new to it), but you could certainly create a helper function that used a separate cursor for each field, and iterated over them to see which records met all the criteria.

Lazuli

Answer 4

Yes, opening continuous key range on an index is pretty much that is in indexedDB. Testing for multiple condition is not possible in IndexedDB. It must be done on cursor loop.

If you find the solution, please let me know.

BTW, i think looping cursor could be very fast and require less memory than possible with Sqlite.

Anchoveta

Answer 5

I'm a couple of years late, but I'd just like to point out that Josh's answer works on the presumption that all of the "columns" in the condition are part of the index's keyPath.

If any of said "columns" exist outside the the index's keyPath, you will have to test the conditions involving them on each entry which the cursor created in the example iterates over. So if you're dealing with such queries, or your index isn't unique, be prepared to write some iteration code!

In any case, I suggest you check out BakedGoods if you can represent your query as a boolean expression.

For these types of operations, it will always open a cursor on the focal objectStore unless you're performing a strict equality query (x ===? y, given x is an objectStore or index key), but it will save you the trouble writing your own cursor iteration code:

bakedGoods.getAll({
    filter: "keyObj > 5 && valueObj.someProperty !== 'someValue'",
    storageTypes: ["indexedDB"],
    complete: function(byStorageTypeResultDataObj, byStorageTypeErrorObj){}
});

Just for the sake of complete transparency, BakedGoods is maintained by moi.

Veriee