How to group by two columns in mysql

I am building a chat website, I have a table that contains the following columns, from_id, to_id, messages.

The from_id is the id of the user that the chat is from and the to_id is the id of the user the chat is sent to.

Now I want to get all the chats but I also want to group it in such a way that if the to_id is my id, it will group it by from_id and if the from_id is my user id, then it will group it by to_id, I tried this SELECT * FROM chats WHERE from_id=:me || to_id=:me GROUP BY from_id, to_id but it only group it when the from_id and the to_id are the same, how can I also make it go the other way round too

Answer

You can do it with a CASE expression:

SELECT CASE WHEN from_id = :me THEN to_id ELSE from_id END id,
       GROUP_CONCAT(messages) messages -- I added this as an aggregate function
FROM chats 
WHERE :me IN (from_id, to_id) 
GROUP BY id

But if you don’t need any aggregation, then you should not use GROUP BY, but DISTINCT:

SELECT DISTINCT CASE WHEN from_id = :me THEN to_id ELSE from_id END id
FROM chats 
WHERE :me IN (from_id, to_id)