For example if i have this table of messages and it has this 3 columns
╔══════════╦══════════╦══════════╦══════════╦
║ from_user║ to_user ║ message ║ date ║
╠══════════╬══════════╣══════════║══════════║
║ 1 ║ 2 ║ text ║timestamp ║
║ 1 ║ 3 ║ text ║ .. ║
║ 2 ║ 1 ║ text ║ .. ║
║ 2 ║ 3 ║ text ║ .. ║
║ 3 ║ 1 ║ text ║ .. ║
║ 1 ║ 2 ║ text ║ .. ║
║ 1 ║ 4 ║ text ║ .. ║
║ 3 ║ 1 ║ text ║ .. ║
╚══════════╩══════════╩══════════╩══════════╩
I want to get all where a user is involved, if i wanted to select all conversations user 1 had (all records where he is in in column "from _user" AND where he is in "to_user) this "conversations" would be:
So i would get only 1 record (the last one) of each of this conversations ordered by date
╔══════════╦══════════╦══════════╦══════════╦
║ from_user║ to_user ║ message ║ date ║
╠══════════╬══════════╣══════════║══════════║
║ 1 ║ 2 ║ text ║timestamp ║
║ 1 ║ 3 ║ text ║ .. ║
║ 2 ║ 1 ║ text ║ .. ║
║ 2 ║ 3 ║ text ║ .. ║
║ 3 ║ 1 ║ text ║ .. ║
║ 1 ║ 2 ║ text ║ .. ║<--- i would get this one third (conv between 1&2)
║ 2 ║ 3 ║ text ║ .. ║
║ 1 ║ 4 ║ text ║ .. ║<--- i would get this one second (conv between 1&4)
║ 3 ║ 1 ║ text ║ .. ║<--- i would get this one first (conv between 1&3)
╚══════════╩══════════╩══════════╩══════════╩
I'm unsure as to how to solve this, should i use GROUP BY ?
EDIT: a conversation is when a user sends or receives a message, a conversation can have multiple messages or just a single one. The ones i marked that i wanted to get as a result are the last record of each of this conversations, it doesn't matter who sent it and who received it i want the last record of each of the conversation a user had.
This attempt is the closest i can get to what i want
SELECT id, from_user, to_user
FROM messages
WHERE (to_user = '$usr_id' OR from_user = '$usr_id') AND id IN
(
SELECT MAX(id)
FROM messages
GROUP BY from_user, to_user
)
but i'm getting the last record of each of the combinations, for example if had
id from_user to_user
1 1 2
2 1 3
3 4 1
4 2 1
5 1 2
the output is:
id from_user to_user
1 1 2
2 1 3
3 4 1
4 2 1
As you can see the record with id of 5 wasn't selected because it's repeated but the records with id 1 and 4 are the same conversation and only one of them should be outputed
For readability purposes, I'll show you a first query that gives the results you want, except if the queried user is on the to_user column:
SELECT from_user, to_user, max(msg_date) latest, id
FROM messages
WHERE to_user = 1
OR from_user = 1
GROUP BY from_user, to_user
ORDER BY latest desc;
To get this solved using group by, you need to switch the from_user, to_user column values when your user is on the to_user side. You might also need a flag, "switched", to indicate these cases. So, what you need is:
SELECT id, main_user, other_user, switched, max(msg_date) latest, msg_date, msg
FROM (SELECT id, 1 main_user, if (from_user = 1, to_user, from_user) other_user,
if (from_user=1, 0, 1) switched, msg_date, msg
FROM messages
WHERE to_user = 1
OR from_user = 1) user_messages
GROUP BY main_user, other_user
ORDER BY msg_date desc;
In this same query, you can use "switched" to put back from_user, to_user, using IF like in the subquery. I didn't put it now to make it easier to read.