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

**URL:** <https://forum.heroiclabs.com/t/get-the-most-recent-user-group-chat-messages-for-a-user-api/3751>\
**Category:** Runtime Framework\
**Created:** [April 4, 2023, 11:16am UTC](https://forum.heroiclabs.com/t/get-the-most-recent-user-group-chat-messages-for-a-user-api/3751 "2023-04-04T11:16:44Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![orta](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.heroiclabs.com/orta/32/1610_2.png) [@orta](https://forum.heroiclabs.com/u/orta)\
**Post date:** [April 4, 2023, 11:16am UTC](https://forum.heroiclabs.com/t/get-the-most-recent-user-group-chat-messages-for-a-user-api/3751/1 "2023-04-04T11:16:44Z")

</div>

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.

```auto
	(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:

```auto
  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