© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
SupabaseS
Supabase•4y ago•
1 reply
lewisd

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

Aplogies for using
camalCase
camalCase


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

-
createdAt
createdAt
(when the conversation was created)
-
id
id
(of the conversation)
-
membersData
membersData
(username, avatar and id from all users involved in this conversation)
-
latestMessage
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
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
Supabase banner
SupabaseJoin
Supabase gives you the tools, documentation, and community that makes managing databases, authentication, and backend infrastructure a lot less overwhelming.
45,816Members
Resources

Similar Threads

Was this page helpful?
Recent Announcements

Similar Threads

How can I rename the variable when I select a table?
SupabaseSSupabase / help-and-questions
4y ago
How can I use a join table to then select from a 3rd table
SupabaseSSupabase / help-and-questions
4y ago
Find rows that aren't linked to another join table (Supabase js)
SupabaseSSupabase / help-and-questions
4y ago
Hello, how can I know what my email address is for my account linked to an specific project?
SupabaseSSupabase / help-and-questions
6mo ago