I'm trying to pull out of the db a list of each last message the current user had sent to/received from another, like the facebook current messaging box (contains a list of "conversations")
Example data:
msg_id sender_id target_id date_sent content ...
1 2 4 20 bla ...
2 2 5 21 bla ...
3 2 6 22 bla ...
4 4 2 25 bla ...
5 5 6 26 bla ...
6 4 2 50 bla ...
If the current user is 2, then I want to get only the last message 2 had with anyone else (sent or received)
Wished data would be:
msg_id sender_id target_id date_sent content ...
6 4 2 50 bla ...
2 2 5 21 bla ...
3 2 6 22 bla ...
msg_id 6 is there because in all the messages 2 and 4 had (regardless of who is the sender/receiver) it has the greatest date (50) msg_id 2 and 3 are there because that's the latest msg 2 had in conversation with users 5 and 6 (one msg to each, sent)
Couldn't find the way to pull this off, should involve a group_by on some uniquely generated field containing both sender and receiver IDs? I don't know, help please
UPDATE:
I liked the ideas, eventually I created a view of that table with another new field, that contains a concatenation of the two Id's, in order (bigger first), seperated by an underscore. This way that field is unique to each conversation. Then group by it :)
That could be a bit messy.
Had a quick play and something like this will do the job. However this will get the latest message sent to a sender AND the latest sent by a sender (rather than the latest one of those which it seems you want), along with the latest message to each recipient from that sender.
Not tested so please excuse any typos.
SELECT a.msg_id, a.sender_id, a.target_id, a.date_sent, a.content
FROM someTable a
INNER JOIN (SELECT sender_id As SomeBod, MAX(date_sent) AS date_sent
FROM someTable
WHERE sender_id = 2
GROUP BY sender_id) sub1
ON a.sender_id = sub1.SomeBod
AND a.date_sent = sub1.date_sent
UNION
SELECT b.msg_id, b.sender_id, b.target_id, b.date_sent, b.content
FROM someTable b
INNER JOIN (SELECT target_id As SomeBod, MAX(date_sent) AS date_sent
FROM someTable
WHERE target_id = 2
GROUP BY target_id) sub2
ON b.target_id = sub2.SomeBod
AND b.date_sent = sub2.date_sent
UNION
SELECT c.msg_id, c.sender_id, c.target_id, c.date_sent, c.content
FROM someTable c
INNER JOIN (SELECT sender_id, target_id, MAX(date_sent) AS date_sent
WHERE sender_id = 2
GROUP BY sender_id, target_id) sub3
ON a.sender_id = sub3.sender_id
AND b.target_id = sub3.target_id
AND b.date_sent = sub3.date_sent
You could probably do something where you combine sender_id and target_id into a computed value and then do a "LIKE" query on that.
But, to be honest, I suspect your performance will be better just running two simpler queries and then merging the results in your code. Your code will be easier to maintain, as well.
Here's an untested example. There are other ways to do it, this is a pretty common question if you do a search.
SELECT t1.*
FROM
msg_table AS t1
LEFT JOIN msg_table AS t2
ON ((t1.sender_id = t2.sender_id AND t1.target_id = t2.target_id)
OR (t1.sender_id = t2.target_id AND t1.target_id = t2.sender_id))
AND t1.msg_id < t2.msg_id
WHERE (t1.sender_id = ? OR t1.target_id = ?) AND t2.msg_id IS NULL
I did something similar to what you're shooting for here. But to make it easier, i added another column to the table called convoID. This is not a unique id, but an id that is reused every time the user sends or receives a message from a user they have allready communicated with. then get all the convoIDs from the table(but not duplicates)
SELECT DISTINCT convoID FROM table WHERE sender='$user' OR recipient='$user'
This way every message between the same 2 people is easy to access as a conversation.From there you can get the newest message from a conversation like this:
SELECT message FROM table WHERE convoID='$thisConvoID' ORDER BY time DESC LIMIT 0,1;
For me this was the least confusing way to do it, and worked great- if you need clarification on anything just ask.