Question 89545 in PostgreSQL Question posted in
The official documentation can be found here.

How to add a subquery to a query? – Postgresql

How to add a subquery to the query to display the last message in the room?

Chat rooms

id, type(USER_TO_USER, USER_TO_SHOP, USER_TO_PLATFORM), name

list of messages

room_messages - id, participant_id, text, is_read

my query

select r.id, r.name from rooms as r
left join room_participants rp on r.id = rp.room_id
where rp."participantType" = 'USER' and rp.participant_id = 1
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Tweaked a little

    SELECT r.id
      ,r.type
      ,r.name
      ,rm.text
      ,rmf.file
FROM rooms r
LEFT JOIN room_participants rp
    on r.id = rp.room_id
INNER JOIN
(
      SELECT room_id, MAX(id) AS max_id
      FROM room_messages
      GROUP BY room_id
) rm_max
    ON r.id = rm_max.room_id
INNER JOIN room_messages rm
    ON r.id = rm.room_id AND rm_max.max_id = rm.id
LEFT JOIN room_message_files rmf
    ON rm.id = rmf.room_message_id
where rp."participantType" = 'USER' and rp.participant_id = 1
ORDER BY rm.room_id;

  2. 0

    You need to first get the last message of each room, then join the rest tables:

    SELECT r.id
      ,r.type
      ,r.name
      ,rm.text
FROM rooms r
INNER JOIN 
(
      SELECT participant_id, MAX(id) AS max_id
      FROM room_messages 
      GROUP BY participant_id
) rm_max 
    ON rm.id = rm_max.participant_id 
INNER JOIN room_messages rm
    ON r.id = rm.participant_id
    AND rm_max.max_id = rm.id
ORDER BY rm.chat_room_id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search