Counting unread messages messing with results?

Question

Currently I have the following:

select *
from (SELECT 'MesRec' as Type, a.Message, a.UserFrom as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserFrom = b.UserFrom AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserFrom
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserTo = 1 AND b.DateSent IS NULL
UNION ALL
SELECT 'MesSen' as Type, a.Message, a.UserTo as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserTo = b.UserTo AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserTo
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserFrom = 1 AND b.DateSent IS NULL
) t
GROUP BY UserID

Which is getting all messages for User 1 from the message table and grouping them together by the last message in that group then if it's a sent message Type is MesSen if it's received it's MesRes, which is perfect.

However I also want to be able to count the "ReadDate" column and if ReadDate is null, to count how many unread messages there are. I've added sum(case when a.DateRead is null then 1 else 0 end) as Qty as follows:

select *
from (SELECT 'MesRec' as Type, a.Message, a.UserFrom as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, sum(case when a.DateRead is null then 1 else 0 end) as Qty
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserFrom = b.UserFrom AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserFrom
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserTo = 1 AND b.DateSent IS NULL
UNION ALL
SELECT 'MesSen' as Type, a.Message, a.UserTo as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, 0 as Qty
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserTo = b.UserTo AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserTo
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserFrom = 1 AND b.DateSent IS NULL
) t
GROUP BY UserID
ORDER BY DateSent DESC

Which is adding the Qty count of unread messages, however it's adding the unread quantity to the wrong row and no longer getting the most recent messages.

Edit:

Example Data without unread count:

Type | Message | UserID | Name | TeamID | TeamName | DateSent
MesRec | Last mess | 3 | Jimmy | 3 | Team 3 | 2022-08-28 19:06:14
MesRec | Test 1 | 4 | Alan | 4 | Team 4 | 2022-08-28 12:50:04
MesSen | Lorem | 5 | Sam | 5 | Team 5 | 2022-08-27 19:25:09

Example Data:

Type | Message | UserID | Name | TeamID | TeamName | DateSent | Qty
MesSen | Test... | 3 | Jimmy | 3 | Team 3 | 2022-08-28 14:40:33 | 0
MesRec | Test 1 | 4 | Alan | 4 | Team 4 | 2022-08-28 12:50:04 | 1
MesSen | Lorem | 5 | Sam | 5 | Team 5 | 2022-08-27 19:25:09 | 0

As you can see when the unread count is on, it is missing the last message received by user 3, and returning it as a message sent, however it's marking the message from user 4 as unread.

EDIT:

I've updated the query to the following:

select Type, Message, UserID, Name, TeamID TeamName, DateSent, sum(case when DateRead is null then 1 else 0 end) as Qty
from (SELECT 'MesRec' as Type, a.Message, a.UserFrom as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, a.DateRead
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserFrom = b.UserFrom AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserFrom
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserTo = 1 AND b.DateSent IS NULL
UNION ALL
SELECT 'MesSen' as Type, a.Message, a.UserTo as UserID, u.Name, u.TeamID, t.TeamName, a.DateSent, NOW() as DateRead
FROM Messages a
LEFT OUTER JOIN Messages b
    ON a.UserTo = b.UserTo AND a.DateSent < b.DateSent
JOIN Users u on u.UserID = a.UserTo
INNER JOIN Teams t on u.TeamID = t.TeamID
WHERE a.UserFrom = 1 AND b.DateSent IS NULL
) t
GROUP BY UserID
ORDER BY DateSent DESC

Which appears to be returning the values as expected now, I addedNOW() to the MesSen type so that it always returns a date, as it doesn't affect sent messages and have done the count on the main select.

Answer 1

UNION ALL is not the best approach for this task. A FULL OUTER JOIN of sent and received messages might be a good approach, but MySQL does not support full outer joins.

In below query I just pick all messages involving user #1, aggregate them by the other user and use conditional aggregation (CASE expression inside the aggregation function) to get the information I want. As this gives me for each conversation the partner and the last message datetime, I can join the message table again to get that last message.

You may want to replace SELECT * with the columns you really want to see.

select *
from
(
  select
    case when userfrom = 1 then userto else userfrom end as other_user_id,
    count(*) as total_count,
    count(case when userfrom = 1 then 1 end) as sent_count,
    count(case when userto = 1 then 1 end) as received_count,
    count(case when userfrom = 1 and readdate is null then 1 end) as sent_unread,
    count(case when userto = 1 and readdate is null then 1 end) as received_unread,
    max(case when userfrom = 1 then datesent end) as last_sent_date,
    max(case when userto = 1 then datesent end) as last_received_date,
    max(datesent) as last_msg_date
  from messages
  where 1 in (userfrom, userto)
  group by case when userfrom = 1 then userto else userfrom end
) aggregated
join users u on u.userid = aggregated.other_user_id
join teams t on t.teamid = u.teamid
left join messages m on 1 in (m.userfrom, m.userto)
                     and aggregated.other_user_id in (m.userfrom, m.userto)
                     and m.datesent = aggregated.last_msg_date
order by aggregated.other_user_id;