javascriptindexeddbdatabase-indexes

Using IndexedDB Like SQL. Query with multiple index values that are not in a range


Basically I want to select all values that match the index/key but they are not in a range programmatically. It'd have to be able to select a range of requests(as in a variable amount at a time). In SQL I'd do it like this.

Select * FROM questions WHERE `id` in (2,45,17)

But when it comes indexedDB my options seem limited. I found that I can do multiple requests in series but how would I do that without already knowing how many are going to be chosen before hand?

Here's the current JavaScript I am utilizing. Obviously this doesn't work. I have already created an indexedDB database named "practice_materials", created an objectstore named "questions", made the index by id, and put data into it.

window.idb=window.indexedDB;
     var request=indexedDB.open(db_name,db_version);
request.onerror=function(event){
    console.log('error:'+event);
}
request.onsuccess=function(event){
  db_res=request.result;
}
var tx=db_res.transaction([obj_store]).objectStore(obj_store);
var res='';     
var out_obj=[];
var id=[2,45,17];
for(i=0;i<3;++i){
    request=tx.get(id[i]);
    request.onsuccess=function(event){
    out_obj.push(request.result);
    }
}

To any future people who come across this question. utilize idb to get out of callback hell so that you can have your code wait until the database/other transactions are complete before you try to access it.

https://github.com/jakearchibald/idb

The only thing is you have to build it for your browser which is worth it to have promises and being able to wait until the transaction completes.(Primarily when opening the database, getting the indexes, selecting objectstore etc., the setting of data don't matter as much but it's still nice to have. P.S. I'm going to be utilizing this for a question & answer system as a study aide for Anatomy students. It'll be selecting a random amount of questions from each chapter(that I've made) and the range will be from 15-25 with the questions being selected randomly. I would utilize WebSQL as I can probably do this a lot easier but it's been appreciated.

P.P.S. Besides a +Rep for correct answer I'll include a link back to this question.


Solution

  • There is no direct route to emulating SQL in statement. Here are two solutions.

    Using Array.prototype.filter() (one may argue that two lines of code is pretty easy)

    const filter = [2,45,17];
    const fieldToFilter = "someField";
    objectStore.getAll().onsuccess = function(event) {
      const filtered = event.target.result.filter(i => filter.includes(i[fieldToFilter]));
      console.log(filtered);
    };
    

    Using IndexDB cursors:

    Attribution: adapted from: Here

    let i = 0;
    const selected = [];
    const filter = [2,17,45];
    
    myIndex.openCursor(keyRangeValue).onsuccess = function(event) {
      let cursor = event.target.result;
      if (!cursor) { // We are done
        console.log(selected); // do something with result
        return;
      } 
      var key = cursor.key;
      if (key > filter[i]) { // filter should be sorted asc
        ++i;
        if (i >= filter.length) {
          return;
        }
      }
      if (key === filter[i]) { // Use individual row
        selected.push(cursor.value); // here
        cursor.continue(); // Next
      } else {
        cursor.continue(filter[i]); // Go to the next filtered key
      }
    }