How to optimize requests? Hello there is a chat which consists of the following tables

Chat rooms

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

Chat Room Members room_participants

id, participantType (USER, SHOP, PLATFORM), participant_id

list of messages

room_messages - id, participant_id, text, is_read

Additionally:

users table

id, first_name, last_name, image, image_src

shops table

id, name, logo, logo_src

The task is:

Display a list of the room with its members for a specific user + the last message of the room (as VK)
the first thing I did was a request to get all the rooms: in which a specific user is a member

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

the second request I make is to get members (other than the current user) for each room

select
    room_id,
    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT id FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT id FROM shops WHERE id = participant_id)
    END AS participant_id,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT first_name FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT name FROM shops WHERE id = participant_id)
    END AS participant_name,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo FROM shops WHERE id = participant_id)
    END AS participant_image,

    case
        when "participantType" = 'USER' and participant_id != 1 then (SELECT image_src FROM users WHERE id = participant_id)
        when "participantType" = 'SHOP' then (SELECT logo_src FROM shops WHERE id = participant_id)
    END AS participant_image_src

from room_participants

where room_id in (1)

The question is, how can these queries be optimized?

and the second question, if these are optimal queries, then how with the last query, how to remove empty lines

>Solution :

You can try using joins instead sub-queries:

select
    room_id,
    COALESCE(u.id, s.id) AS participant_id,
    COALESCE(u.first_name, s.name) AS participant_name,
    COALESCE(u.image, s.logo) AS participant_image,
    COALESCE(u.image_src, s.logo_src) AS participant_image_src

from room_participants p
left join shops s
    on p.participant_id = s.id
    and p.participantType = 'SHOP'
left join users u
    on p.participant_id = u.id
    and p.participantType = 'USER'
    AND p.participant_id != 1

where room_id in (1)
    AND COALESCE(u.id, s.id) IS NOT NULL