按更多组合分组

Question

I have the following table to represent a conversation between users.

message | user1 | user2 | unixtime
msg1    | 14    | 21    | -
msg2    | 21    | 12    | -
msg1    | 14    | 18    | -
msg3    | 14    | 21    | -
msg2    | 18    | 14    | -
msg4    | 21    | 12    | -

I want to show a list of a a specific user's conversations by selecting all messages with current_user = user1 or user2. So far this SELECT will select the correct messages but the grouping doesn't work correctly.

SELECT * FROM (
    SELECT * FROM messages
    WHERE user1 = '".$_SESSION['login']['ID']."'
    OR user2 = '".$_SESSION['login']['ID']."'
    ORDER BY unixtime DESC
) as list
GROUP BY user1, user2
ORDER BY unixtime DESC

It will only group the messages with the same user1 and user2 and not if the two users are reversed. Is this done by a more advanced grouping or should I join the table in some way?

Answer 1

It is unclear what you want the query to do, because you are using group by and select *. The select * returns indeterminate values from matching rows. I don't know why anyone would want to do this. You can read about this in the documentation.

If you want to counts the messages:

SELECT least(user1, user2), greatest(user1, user2), count(*)
FROM messages
WHERE '".$_SESSION['login']['ID']."' in (user1, user2)
GROUP BY least(user1, user2), greatest(user1, user2)