0

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

My messages table is called conversationMessages and a row looks like this:

enter image description here

2
  • Please consider following these suggestions.
    – mustaccio
    Sep 20 at 15:32
  • This needs more clarity. What are the CREATE TABLE statements? Which column of which table is the "latest message"? Sep 21 at 6:38

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.