Multiple nested filters to get specific values

[idi527]

You can probably use a lateral join to preload the newest message for each chat.

Preloading top comments for posts in Ecto

Maybe https://hexdocs.pm/ecto/Ecto.Query.html#join/5-joining-with-fragments would help. I guess SELECT comments.* FROM posts JOIN LATERAL ( SELECT * FROM comments WHERE comments.posts_id = posts.id ORDER BY score DESC LIMIT 3 ) comments ON true WHERE posts.id IN (1, 2, 3) ORDER BY posts.id; might be rewritten with ecto as defmacrop last_comments(post_id, limit \\ 3) do quote do fragment( "SELECT * FROM comments WHERE comments.post_id = ? ORDER BY score DESC LIMIT ?", …

---

As for your code snippet, it might be simplified to

%{chat_users: chat_users} = result

Enum.map(chat_users, fn
  %{chat_messages: [first_message | _rest]} = chat_user -> {chat_user, first_message}
  chat_user -> {chat_user, nil}
end)

It would return a list of user/message tuples with the latter possibly being nil.