Is there a way to sort using a composite/compound key in indexedDB?

Question

In How to use IndexedDB count method with composite keys? I learned how to filter for single attributes. So far, so good, but now I need to sort and filter at the same time.

My object store is filled with these objects:

os.put({feed_id:1,id:1,title:"foofoo",time:111});
os.put({feed_id:1,id:2,title:"foobar",time:112});
os.put({feed_id:1,id:3,title:"foobaz",time:113});
os.put({feed_id:3,id:1,title:"bazfoo",time:114});
os.put({feed_id:3,id:2,title:"bazbar",time:115});
os.put({feed_id:3,id:3,title:"bazbaz",time:116});
os.put({feed_id:1,id:4,title:"foofoo",time:122});
os.put({feed_id:1,id:5,title:"foobar",time:121});
os.put({feed_id:1,id:6,title:"foobaz",time:120});
os.put({feed_id:3,id:4,title:"bazfoo",time:119});
os.put({feed_id:3,id:5,title:"bazbar",time:118});
os.put({feed_id:3,id:6,title:"bazbaz",time:117});

I have two indices set on the object store, one (idx_feedid) has the keyPath feed_id, which allows me to get all the objects with a specific feed id using openCursor, and one (idx_ts) with keyPath ["id","feed_id","time"].

The problem is that iterating over the results of idx_feedid gives me the results without ordering on the timestamp. In SQL I'd easily do a SELECT * FROM feeditems WHERE feed_id=3 ORDER BY time DESC, but how does one do this in IndexedDB?

And how can I limit the results returned to achieve pagination? In essence I'd need something like MySQLs LIMIT 0,10/LIMIT 10,10/...

possible duplicate of [In IndexedDB, is there a way to make a sorted compound query?](http://stackoverflow.com/questions/12084177/in-indexeddb-is-there-a-way-to-make-a-sorted-compound-query) — Josh, May 22 '14 at 19:55

Josh · Answer 1 · 2013-12-06T15:44:38.737

Can't do limits. Even if you try to, the internal implementations use optimizations over which you have no control, such as always loading the first 100 rows into RAM, even when you only want 1. Luckily, small scale apps are not too heavily affected by this because it is still pretty fast.

Regarding your problem, take a look at https://developer.mozilla.org/en-US/docs/Web/API/IDBObjectStore?redirectlocale=en-US&redirectslug=IndexedDB%2FIDBObjectStore#openCursor(). Setup an IDBKeyRange on feed id 3, and pass in a direction like 'prev' to iterate in descending order.

Edit: to do pagination you would use cursor.advance(+ or - n rows)

Maybe you can also help me with this question: http://stackoverflow.com/questions/26546716/sort-by-number-date — John Smith, Oct 24 '14 at 11:31

score -1 · Answer 2 · answered Nov 01 '13 at 03:19

Use compound index, ['feed_id', 'time'] and query over IDBKeyRange.only([3]) on reverse direction.

Using my open source library, it is dowe as follow:

var schema = {
  stores: [{
    name: 'os',
    indexes: [{
      name: 'feed_id, time',
      keyPath: ['feed_id', 'time']
    }]
  }]
}

var db = new ydn.db.Storage('db name', schema);
var kr = ydn.db.KeyRange.starts([3]);
db.values('os', 'feed_id, time', kr, 10, 0, true).done(function(values) {
  console.log(values); // sorted by descending of time
});

The library support both indexeddb and websql without any dependency. You will also see very fast performance on websql.

If you use shim, compound index may not work properly.

Is there a way to sort using a composite/compound key in indexedDB?

2 Answers2