Postgresql - Aggregate functions are not allowed in FILTER

BoboBoy
October 31, 2022
237 views
0 votes
2 Answers

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

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?

Answers

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;

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;

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – Aggregate functions are not allowed in FILTER

Answers