Get the most recent user/group chat messages for a user API

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