SupabaseS
Supabase•4y ago
lewisd

How can I select the latest message that's linked to my conversation table?

Aplogies for using camalCase

I need to select a few fields, in total I want:

  • createdAt (when the conversation was created)
  • id
     (of the conversation)
  • membersData (username, avatar and id from all users involved in this conversation)
  • latestMessage (The latest message... struggling with this part)
My query so far:
select 
  conversations."createdAt",
  conversations.id,
  array_agg(json_build_object('id', "conversationMembers"."userId", 'avatar', "userData".avatar, 'username', "userData".username)) as "membersData"
from 
  conversations
join 
  "conversationMembers" on "conversationMembers"."conversationId" = conversations.id
join 
  "userData" on "conversationMembers"."userId" = "userData".id
group by 
  conversations.id
order by 
  conversations."createdAt" desc
Was this page helpful?