I have a store with multiple indexes I want to query. For the sake of the example, let's assume I have a store of messages with a user_id index and create_date which is timestamp (and let's assume I have indexes - user_id / create_date / user_id, create_date
I know how to query for user by id:
var range = IDBKeyRange.only('123');
store.index('user_id').openCursor(range).onsuccess= function(e){....};
And I know how to query by date:
var range = IDBKeyRange.lowerBound(Date.now()-24 * 60 * 1000))
store.index('create_data').openCursor(range).onsuccess = function(e){....};
Buy I could not figure out how to query both together. I know that I can use JS parse the results for either of the 2, but I wanted to know if it's possible to do this using IDB.
edit - the pseudo-query I want to do is
user_id=123 AND create_date < (NOW() - 24 * 60 * 1000)
Thanks!
I have a store with multiple indexes I want to query. For the sake of the example, let's assume I have a store of messages with a user_id index and create_date which is timestamp (and let's assume I have indexes - user_id / create_date / user_id, create_date
I know how to query for user by id:
var range = IDBKeyRange.only('123');
store.index('user_id').openCursor(range).onsuccess= function(e){....};
And I know how to query by date:
var range = IDBKeyRange.lowerBound(Date.now()-24 * 60 * 1000))
store.index('create_data').openCursor(range).onsuccess = function(e){....};
Buy I could not figure out how to query both together. I know that I can use JS parse the results for either of the 2, but I wanted to know if it's possible to do this using IDB.
edit - the pseudo-query I want to do is
user_id=123 AND create_date < (NOW() - 24 * 60 * 1000)
Thanks!
Share Improve this question edited Jul 14, 2015 at 10:27 AriehGlazer asked Jul 14, 2015 at 10:22 AriehGlazerAriehGlazer 2,3209 gold badges27 silver badges31 bronze badges 1- I am viewing documentation but I can't figure how to make it. If IDBKeyRange.only() returns an array, you can mix two arrays in one and you have the plete range. If returns an object is more plex and may other users to help you because I don't know. Sorry :( – Marcos Pérez Gude Commented Jul 14, 2015 at 10:43
2 Answers
Reset to default 5A working example below.
<!DOCTYPE html>
<html lang="en">
<head>
<title>Stackoverflow</title>
<script type="text/javascript" charset="utf-8">
var db_handler = null;
var dbDeleteRequest = window.indexedDB.deleteDatabase("toDoList");
dbDeleteRequest.onerror = function(event) {
console.log("Error while deleting database.", true);
};
dbDeleteRequest.onsuccess = function(event) {
// Let us open our database
var DBOpenRequest = window.indexedDB.open("toDoList", 5);
DBOpenRequest.onsuccess = function(event) {
console.log('<li>Database initialised.</li>');
// store the result of opening the database in the db variable. This is used a lot below
db_handler = DBOpenRequest.result;
// Run the addData() function to add the data to the database
addData();
};
DBOpenRequest.onupgradeneeded = function(event) {
console.log('<li>DBOpenRequest.onupgradeneeded</li>');
var db = event.target.result;
db.onerror = function(event) {
console.log('<li>Error loading database.</li>');
};
// Create an objectStore for this database //, { keyPath: "taskTitle" }); { autoIncrement : true }
var objectStore = db.createObjectStore("toDoList", { autoIncrement : true });
// define what data items the objectStore will contain
objectStore.createIndex("user_id", "user_id", { unique: false });
objectStore.createIndex("create_data", "create_data", { unique: false });
objectStore.createIndex("tags",['user_id','create_data'], {unique:false});
};
};
function addData() {
// Create a new object ready to insert into the IDB
var newItem = [];
newItem.push({ user_id: "101", create_data: (1000)});
newItem.push({ user_id: "102", create_data: (Date.now() - 18 * 60 * 1000)});
newItem.push({ user_id: "103", create_data: (Date.now() - 18 * 60 * 1000)});
newItem.push({ user_id: "101", create_data: (2000)});
newItem.push({ user_id: "101", create_data: (989)});
newItem.push({ user_id: "104", create_data: (Date.now() - 18 * 60 * 1000)});
console.log(newItem);
// open a read/write db transaction, ready for adding the data
var transaction = db_handler.transaction(["toDoList"], "readwrite");
// report on the success of opening the transaction
transaction.onplete = function(event) {
console.log('<li>Transaction pleted: database modification finished.</li>' + new Date());
};
transaction.onerror = function(event) {
console.log('<li>Transaction not opened due to error. Duplicate items not allowed.</li>');
};
// create an object store on the transaction
var objectStore = transaction.objectStore("toDoList");
addData2(transaction, objectStore, newItem, 0, true);
};
function addData2(txn, store, records, i, mitT) {
try {
if (i < records.length) {
var rec = records[i];
var req = store.add(rec);
req.onsuccess = function(ev) {
i++;
console.log("Adding record " + i + " " + new Date());
addData2(txn, store, records, i, mitT);
return;
}
req.onerror = function(ev) {
console.log("Failed to add record." + " Error: " + ev.message);
}
} else if (i == records.length) {
console.log('Finished adding ' + records.length + " records");
}
} catch (e) {
console.log(e.message);
}
//console.log("#########")
};
function select() {
var transaction = db_handler.transaction('toDoList','readonly');
var store = transaction.objectStore('toDoList');
var index = store.index('tags');
var range = IDBKeyRange.bound(['101', 999],['101', 2001]);
var req = index.openCursor(range);
req.onsuccess = function(e){
var cursor = e.target.result;
if (cursor) {
if(cursor.value != null && cursor.value != undefined){
console.log(cursor.value);
}
cursor["continue"]();
}
}
}
</script>
</head>
<body>
<div id="selectButton">
<button onclick="select()">Select Data</button>
<input type="text" id="selectData" value="">
</div>
</body>
</html>
Key points and concepts to solve the problem statement you have:
- Requirement is to search values from more than one property, and a range bound search. So, you need
- A plex/pound index covering the properties you want to search (
IDBObjectStore.createIndex()), so that you can search more than one property. - A range based search, so -
IDBKeyRange.bound()
- A plex/pound index covering the properties you want to search (
In the example above,
- plex or pound index is created using
objectStore.createIndex("tags",['user_id','create_data'], {unique:false}); - range is created using
var range = IDBKeyRange.bound(['101', 1000],['101', 2000]);
A word of caution:
You need is very well served with var range = IDBKeyRange.bound(['101', 1000],['101', 2000]); but be sure about result of this var range = IDBKeyRange.bound(['101', 1000],['103', 2000]);
If you are using plex/pound range then it is like range between 101 to 103 OR 1000 to 2000. It is not AND but OR of the plex/pound range you are specifying.
Try various bination of ranges and you will understand the full power and limits of IDBKeyRange.
The answer is by using IDBKeyRange.bound:
var range = IDBKeyRange.bound(['123'],['123', Date.now()-10*1000]);
store.index('user_id,create_date').getCursor(range).onsuccess = function(e){...}
Basically, all IDBKeyRange ranges can be expressed using the bound range, so by using multiple values we can create any pound range we want