CODEWITHSUNDEEP
javascriptindexeddbdatabase-indexes
133794m3r
133794m3r

Reputation: 5118

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.

Upvotes: 0

Views: 4383

Answers (2)

Ujjwal Kumar Gupta
Ujjwal Kumar Gupta

Reputation: 2376

You can use JsStore which allows you to write code in SQL like apis.

Your SQL query -

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

will be in JsStore -

const query = {
    from: 'questions',
    where: {
        id: {
            in: [2, 45, 17]
        }
    }
};

connection.select(query).then(results => {
    console.log(results);
}).catch(error => {
    console.error(error);
});

This allows you to think in SQL and write code in javascript. Which makes overall indexeddb app development faster.

Here is link to doc - https://jsstore.net/

Upvotes: 0

Randy Casburn
Randy Casburn

Reputation: 14165

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
  }
}

Upvotes: 1

Related Questions