I know similar questions had been asked before, but none of them had this same conditions and their answers didn't work for this case.
The table containing the messages looks like this:
id | owner_id | recipient_id | content | created
1 | 1 | 2 | Hello | 2015-12-08 20:00
2 | 2 | 1 | Hey | 2015-12-08 20:10
3 | 3 | 1 | You there? | 2015-12-08 21:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00
And let's say I query for the last message from each one of the conversations for User ID 1, the expected result is:
id | owner_id | recipient_id | content | created
5 | 4 | 1 | Hey buddy | 2015-12-08 22:00
4 | 1 | 3 | Yes | 2015-12-08 21:15
2 | 2 | 1 | Hey | 2015-12-08 20:10
I tried many combinations, using JOINs and sub-queries but none of them gave the expected results.
Here is one of the queries I tried but it's not working. I believe is not even near to what I'm needing.
SELECT
IF ( owner_id = 1, recipient_id, owner_id ) AS Recipient,
(
SELECT
content
FROM
messages
WHERE
( owner_id = 1 AND recipient_id = Recipient )
OR
( owner_id = Recipient AND recipient_id = 1 )
ORDER BY
created DESC
LIMIT 1
)
FROM
messages
WHERE
owner_id = 1
OR
recipient_id = 1
GROUP BY
Recipient;
select t.*
from
t
join
(select user, max(created) m
from
(
(select id, recipient_id user, created
from t
where owner_id=1 )
union
(select id, owner_id user, created
from t
where recipient_id=1)
) t1
group by user) t2
on ((owner_id=1 and recipient_id=user) or
(owner_id=user and recipient_id=1)) and
(created = m)
order by created desc
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments