Hi folks, as the documentation recommends avoiding making actual SQL queries myself, I’m in the process of going through my codebase and giving examples of cases where I have dropped down to that abstraction in order to help future runtime API ideas
Probably the most complex usage I have of the SQL API is one that gets the most recent message from each chat for a user. E.g. if you were showing a chat list, then it’d be nice to show the most recent message for each user without having to open every chat stream to get a single message.
(select distinct on (sender_id) sender_id, stream_subject, stream_descriptor, content->>'text' as content, create_time from message WHERE
(sender_id = $1 OR stream_subject = $1 or stream_descriptor = $1)
AND stream_mode = 4
order by sender_id, create_time desc)
union
(select distinct on (stream_subject) sender_id, stream_subject, stream_descriptor, content->>'text' as content, create_time from message WHERE
(sender_id = $1 OR stream_subject = $1 or stream_descriptor = $1)
AND stream_mode = 4
order by stream_subject, create_time desc)
This gives you back both chat member’s most recent messages, and so the data requires a bit of massaging, which for me looks a little like:
const responseMap = new Map<string, User>()
for (const d of data) {
const key = d.stream_subject === root.nakamaID ? d.sender_id : d.stream_subject
const value = responseMap.get(key)
if (!value) {
responseMap.set(key, {
lastChatMessageWasViewer: d.sender_id === root.nakamaID,
puzmoNUserID: key,
content: d.content || "...",
createdAt: d.create_time,
})
} else {
if (d.create_time.localeCompare(value.createdAt) === 1) {
value.content = d.content || "..."
value.createdAt = d.create_time
value.lastChatMessageWasViewer = d.sender_id === root.nakamaID
}
}
}
const responseBlurbs = Array.from(responseMap.values())
I’d love there to be maybe three APIs around this sort of thing?
-
nk.userFriendStreamHeads(userID)
- grabs the latest message from all chats for a user -
nk.groupStreamHeads(userID)
- grabs the latest message from all group chats for a user -
nk.streamHeads(streamIDs)
- the underlaying API for the above two fns