Getting top value in each grouped record

Couchbase Server
, ,
1

I am create a poc for with couchbase for our chat application. I am having two types of document ( conversation and chatmessages) , where conversation is the parent and it can have multiple chatmessages.

I would like to get last message i received in each conversation i am involved with.

conversation document 

    {
   "id":"c-1",
      "users": [
        1,
        2
      ],
      "contextType": "private",
      "lastUpdated": "2017-11-01T11:40:35.469Z",
      "type": "conversation"
    }

// message document 


{
  "messageText": "hi how r u? ",
  "sentBy": 2,
  "read": false,
  "status": "delivered",
  "conversationId": "c_1",
  "sentOn": "2017-11-01T11:40:35.469Z",
  "type": "chatMessage"
}

I wrote a query to fetch all messages i am involved like below . Please suggest for optimization and what i am doing is correct or not.

select * from   `chat_dev` as chat where type = "chatMessage" AND EXISTS (
select  meta(con).id from `chat_dev` as con  use keys chat.conversationId where type = "conversation" AND ANY item IN users SATISFIES item = 1 END )
ORDER BY chat.sentOn DESC
2 
CRATE IDEX ix1 ON chat_dev(SentBy) WHERE type = "chatMessage";

SELECT MAX([cm.sentOn,cm])[1] 
FROM chat_dev AS cm 
JOIN chat_dev AS con ON KEYS chat.conversationId
WHERE cm.type = "chatMessage" AND cm.SentBy = 1 AND
              con.type = "conversation" AND cm.SentBy IN con.users
GROUP BY cm.conversationId;
1 Like
3

@vsr1 works like a charm. Thanks . Just one doubt on indexes. Do we need to add sentOn and conversationId also to the index ? will that be good if we need to order and get latest message per conversation ?

say for example we need to take 50/50 messages for that conversation

4

As you are projecting whole document you are not required. If there is predicate you can add them as part of index key.

If you need to group and by conversion and get 50 messages each group not straight forward (SQL limit is for whole query) but it is possible with multiple subqueries each producing 50.

If you are using 4.6.0+ Try the following
For each conversation it gives latest 50 messages as array of objects.

SELECT cm.conversationId, (SELECT cid  FROM cmbyid AS cid ORDER BY cid.sentOn LIMIT 50) AS messages
FROM chat_dev AS cm
JOIN chat_dev AS con ON KEYS chat.conversationId
WHERE cm.type = "chatMessage" AND cm.SentBy = 1 AND
              con.type = "conversation" AND cm.SentBy IN con.users
GROUP BY cm.conversationId
LETTING cmbyid = ARRY_AGG(cm);