You can use a
UNION
of two subqueries:SELECT
user_id,
MAX(ts) AS last_timestamp,
SUM(cnt) AS number_of_messages
FROM
( SELECT to_user_id AS user_id,
MAX(timestamp) AS ts,
COUNT(*) AS cnt
FROM chat
WHERE from_user_id = 1
GROUP BY to_user_id
UNION ALL
SELECT from_user_id AS user_id,
MAX(timestamp) AS ts,
COUNT(*) AS cnt
FROM chat
WHERE to_user_id = 1
GROUP BY from_user_id
) AS tmp
GROUP BY
user_id ;
Two indexes, on (from_user_id, to_user_id, timestamp)
and (to_user_id, from_user_id, timestamp)
would help efficiency of the subqueries.
0 Comments
Good day precious one, We love you more than anything.