IndexedDB query records based on the relationship between two fields in the same store

user_2091847 0
Viewed 56

I have the following SQL:

SELECT * FROM task WHERE finishDate > dueDate;

Is there a way to query an IndexedDB store to implement the function above?

I have an index on both the finishDate and dueDate.

indexeddb
1 Answers

You won't be able to use those indexes for this query - as the value you are searching for is calculated when comparing two properties on the same object.

So if you want to do this without any db change you'll have to iterate through all objects and find the values needed - example here:

function findItemsWithPastDueDate(callback){
    const initOpenReq = indexedDB.open(baseName);
    initOpenReq.onsuccess = function() {
        const db = initOpenReq.result;
        const transaction = db.transaction(objectStoreName, 'readonly');
        const objectStore = transaction.objectStore(objectStoreName);
        const cursorRequest = objectStore.openCursor();
        let aggregate = [];
        cursorRequest.onsuccess = function (event){
            if (event.target.result){
                const val = event.target.result.value;
                if(val.finishDate > && val.dueDate){ 
                    aggregate.push(val);
                }
                event.target.result.continue();
            }
        };

        transaction.oncomplete = function (event) {
                callback(aggregate); // return items
        };
    }
}

Alternate Solution: If this is some query that you'll want to execute fast, then I would suggest you to add a calculated field to all records finishDatePastDueDate and add index on this field - and then you'll be able to query the records on this newly created index.

user_3661140
Related