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?

CodePudding user response：

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:

• Select first row in each GROUP BY group?
• Best way to get result count before LIMIT was applied

CodePudding user response：

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;

CodePudding user response：

I would create a CTE with MAX(messages.created_at) as a column max_created_at and reuse your query with the CTE instead of table messages (w/o looking into its logic).

with t as (select *, MAX(created_at) over () max_created_at from messages)
SELECT -- Basically this is your query that follows with some simplification
    users.id,
    users.name,
    users.avatar,
    max(t.max_created_at),
    MAX(t.body) FILTER (WHERE t.created_at = t.max_created_at) last_message,
    COUNT(t.is_read) FILTER (WHERE not is_read AND t.from_id != 14) = 0 is_read,
    COUNT(t.is_read) FILTER (WHERE not is_read AND t.from_id != 14) count_unread
FROM 
    t 
INNER JOIN 
    users ON t.from_id = users.id OR t.to_id = users.id
WHERE 
    (t.from_id = 14 OR t.to_id = 14) 
    AND users.id != 14
GROUP BY 
    users.id;