Order grouped selection by condition in IndexedDB

Question

I have an object store "messages" with "timestamp" and "chat_id" fields inside it. I want to get all "chats" inside this object store ordered by "timestamp" field. As far as I see, I can't do it with IndexedDB only.

I can get them like this using WebDatabase:

SELECT * FROM messages GROUP BY chat_id ORDER BY timestamp

In IndexedDB:

1. if I create compound index using ["chat_id", "timestamp"], records still won't be sorted by "timestamp" field. Instead they will be sorted by "chat_id" first. So that 2-year-old messages with "chat_id" == 1 will be preceding messages with "chat_id" == 2, written yesterday.
2. if I create simple index of "chat_id" and get records from this index using "nextunique", same chat messages are sorted with primary key. In my case it's message "id".

Am I wrong? Is it possible to sort grouped selection in IndexedDB?

UPD: I want to get all chats "grouped" by chat_id and sort this selection by timestamp. So that newest chats will be the first.

Answer 1

To clarify, you want all messages from the chat_id that contains the message with the lowest timestamp first, then all the (as yet unseen) messages from the chat_id that contains a message the next lowest timestamp, etc?

Indexed DB doesn't provide anything specifically helpful here.

A simple approach would use two indexes: one on timestamp, one on chat_id.

1. Let set be a new empty Set
2. Open an unbounded cursor on the timestamp index
3. For each message seen:
   1. If message's chat_id is in set
      1. continue() the cursor
   2. Otherwise:
      1. add chat_id to set
      2. open a cursor on the chat_id index with IDBKeyRange.only(chat_id) as bounds
      3. fetch all the records

Answer 2

You can use JsStore for doing group by, order by or almost all kind of sql queries in indexeddb. It provides api which is identical to sql.

So lets say your query in sql is -

SELECT * FROM messages GROUP BY chat_id ORDER BY timestamp

In JsStore, it will be -

var Connection = JsStore.Instance('Db_name');
Connection.select({
     From: 'messages',
     GroupBy:'chat_id',
     Order:{
        By:'timestamp'
     },
     OnSuccess:function(results){
        console.log(results);
     },
     OnError:function(error){
        console.log(error);
     }
})

For more check out this link - http://jsstore.net/

Answer 3

It is possible to, as you say, sort a grouped selection in indexedDB. But it is not exactly clear from your question what you are asking (at least to me).

Try using ['timestamp','chat_id'] and just use next/prev instead of nextunique/prevunique. You want to order the fields of the index in the order you want to iterate over the cursor. If you want to sort by time, then put timestamp as the first field. In fact this should have the effect of sorting first by time and then by chat id. Of course, this means that you will have messages that arrived around the same time from different chats appearing in separate places.

If the messages with the same chat id must be consecutive, then just use 'next' and cursor.continue, and continue to use ['chat_id','timestamp']. When iterating just check if chat_id changed from the previous iteration to see if a new chat started.

It is not clear why are you using nextunique here. nextunique is just going to iterate over the entire index/store, assuming no two messages with the same chat id can also have the same timestamp. Pretty much every key will be unique in the index, so this would be kinda pointless.