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

Question

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.

Randy Casburn · Accepted Answer · 2019-05-19 21:47:35Z

1

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

answered May 19, 2019 at 21:47

Randy Casburn

14.2k1 gold badge19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

133794m3r Over a year ago

The first one you posted was what I came up with as a last resort, as I don't want to have to pull the entire database off of disk to just get some random indexes. The second option looks like it'd work. I just have to try it myself and make sure it works before I mark this as solved but it's already upvoted. I guess my searching skills aren't as good as they once were.

Randy Casburn Over a year ago

The cursors and filtering are a mess with IndexDB

133794m3r Over a year ago

I really wish that the mozilla would've thought to include some of the niceties of sql when they decided to kill off WebSQL as it would make my life easier. I figured it'd be a mess when dealing with it but there's no better alternatives. I wish they would've thought this through better during the design phase.

Ujjwal Kumar Gupta · Accepted Answer · 2024-07-07 09:33:09Z

0

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/

answered Jul 7, 2024 at 9:33

Ujjwal Kumar Gupta

2,3961 gold badge25 silver badges34 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related