skip to Main Content

I have this SQL query:

SELECT
    users.id,
    users.name,
    users.avatar,
    MAX(messages.created_at) max_created_at,
    MAX(messages.body) FILTER (WHERE messages.created_at = MAX(messages.created_at)) last_message,
    CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) = 0) THEN true ELSE false END is_read,
    COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) count_unread
FROM 
    messages 
INNER JOIN 
    users ON messages.from_id = users.id OR messages.to_id = users.id
WHERE 
    (messages.from_id = 14 OR messages.to_id = 14) 
    AND users.id != 14
GROUP BY 
    users.id;

But, this query is showing an error

Aggregate functions are not allowed in FILTER

When I change

MAX(messages.body) FILTER (WHERE messages.created_at = MAX(messages.created_at)) last_message

to

MAX(messages.body) FILTER (HAVING messages.created_at = MAX(messages.created_at)) last_message

the query is now showing this error

Syntax error at or near "HAVING"

How to fix this?

2

Answers


  1. I think you require this:

    select a.* from(
    SELECT
        users.id,
        users.name,
        users.avatar,
        MAX(messages.created_at) max_created_at,
        messages.body   last_message,
        CASE WHEN(COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) = 0) THEN true ELSE false END is_read,
        COUNT(messages.is_read) FILTER (WHERE is_read = false AND messages.from_id != 14) count_unread,
            RANK () OVER ( 
            PARTITION BY user_id
            ORDER BY messages.created_at DESC
        ) message_rank 
    FROM 
        messages 
    INNER JOIN 
        users ON messages.from_id = users.id OR messages.to_id = users.id
    WHERE 
        (messages.from_id = 14 OR messages.to_id = 14) 
        AND users.id != 14
    GROUP BY 
        users.id)a
    where message_rank=1;
    
    Login or Signup to reply.
  2. Could be done with window functions and DISTINCT ON in a single instance of SELECT:

    SELECT DISTINCT ON (u.id)
           u.id, u.name, u.avatar
         , m.created_at  AS max_created_at
         , m.body        AS last_message
         , bool_and(is_read) FILTER (WHERE m.from_id <> 14)          OVER w AS is_read
         , count(*) FILTER (WHERE NOT m.is_read AND m.from_id <> 14) OVER w AS count_unread
    FROM   messages m
    JOIN   users    u ON u.id IN (m.from_id, m.to_id)
    WHERE  14 IN (m.from_id, m.to_id)
    AND    u.id <> 14
    WINDOW w AS (PARTITION BY u.id)
    ORDER  BY u.id, m.created_at DESC NULLS LAST, m.body DESC NULLS LAST;
    

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search