sqldatabasepostgresqlaggregate-filter

How to query a chat list with number of unread messages

I have a PostgreSQL table holding chats between two people.

Table columns:

id (PRIMARY KEY)
professional_id (FOREIGN KEY)
request_id (FOREIGN KEY)
message
created_by (FOREIGN KEY)
created_at
read_at

I need to have the list of chats and for each chat I need to have the number of unread messages (i.e. WHERE read_at IS NULL).

This is my query to get the chat list for that request:

SELECT
rm.professional_id,
CONCAT(u.first_name, ' ', u.last_name) AS professional_full_name,
FROM request_messages AS rm 
LEFT JOIN users AS u ON (rm.professional_id = u.id) 
WHERE rm.request_id = $1 
GROUP BY rm.professional_id, professional_full_name

I need a third output column to display a count WHERE read_at IS NULL.

Solution

  • Use the aggregate FILTER clause. See:

    SELECT rm.professional_id
     , concat_ws(' ', u.first_name, u.last_name) AS professional_full_name
     , rm.unread_count
FROM  (
   SELECT professional_id
        , count(*) FILTER (WHERE read_at IS NULL) AS unread_count
   FROM   request_messages
   WHERE  request_id = $1 
   GROUP  BY professional_id
   ) rm
LEFT   JOIN users u ON rm.professional_id = u.id
-- ORDER BY rm.unread_count DESC, professional_full_name  -- ?
;

    Also, it's typically best to aggregate first, and join later:

    About concat_ws():