get unique records between two fields from mysql in php
Get the solution ↓↓↓Solution:
Not very elegant but working:
SELECT m1.*
FROM private_msgs m1 LEFT OUTER JOIN private_msgs m2
ON (m1.message_from_uid=m2.message_from_uid AND m1.message_to_uid=m2.message_to_uid AND m1.message_time<m2.message_time)
WHERE m2.message_id IS NULL AND m1.message_from_uid=$uid
ORDER BY message_time DESC;
Add limit to 5 or whatever you want
EDIT. Sorry I didn't notice that UID can be as sender or receiver. Can you publish table with sample data for example here: http://sqlfiddle.com?
Anyway that should to the trick:
WHERE m2.message_id IS NULL AND (m1.message_from_uid=$uid OR m1.message_to_uid=$uid)
Answer
Solution:
Here it is, long and ugly. You can replace temporary tables as subqueries in one big final query. Explanations are at the end.
CREATE TEMPORARY TABLE IF NOT EXISTS t1 AS
(SELECT message_id, message_from_uid, message_to_uid, message_time
FROM private_msgs
WHERE message_from_uid=$UID)
UNION
(SELECT message_id, message_to_uid, message_from_uid, message_time
FROM private_msgs
WHERE message_to_uid=$UID);
CREATE TEMPORARY TABLE IF NOT EXISTS t2 AS SELECT * FROM t1;
SELECT m.*
FROM (t1 LEFT OUTER JOIN t2
ON (t1.message_from_uid=t2.message_from_uid AND t1.message_to_uid=t2.message_to_uid AND t1.message_time<t2.message_time))
JOIN private_msgs m ON t1.message_id=m.message_id
WHERE t2.message_id IS NULL AND t1.message_from_uid=$UID
ORDER BY t1.message_time DESC;
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
t1 temporary table takes all messages where $UID is a sender and union them with messages where $UID is receiver, but puts $UID as sender. In t1 doesn't matter who is sender/receiver. What does matter that these are conversations between $UID and someone else.
t2 is copy of t1, because in MySQL you can't join temporary table with itself.
And finally we do the query. We join t1 and t2, take only newest messages in every conversation and sort it from latest to oldest. As in t1 and t2 sender and receiver are mixed we do the last join with private_msg to get original data.
PS. Sorry for my english, I'm not native speaker.
Answer
Solution:
Found among my old code:
select m.*
from private_messages m
join user_table s ON m.sender_id = s.id
join user_table r ON m.receiver_id = r.id
join (select if(sender_id = $UID, receiver_id, sender_id) as user_id_other,
max(posted_at) AS date_time_max from private_messages
where (sender_id = $UID OR receiver_id = $UID)
group by if(sender_id=$UID, receiver_id, sender_id)
) AS t
on if(m.sender_id = $UID, m.receiver_id, m.sender_id) = user_id_other
AND m.posted_at=date_time_max
where m.sender_id = $UID OR m.receiver_id = $UID
order by m.posted_at desc
Not responsible for result, but this can help. By itself variables must be replaced, I did not modify the code very much, as you can see. But this piece of... code... worked.
In your case smths like:
select m.*
from YOUR_MESSAGES_TABLE m
--- OPTIONAL: join YOUR_USER_TABLE s ON m.message_from_uid = s.id
--- OPTIONAL: join YOUR_USER_TABLE r ON m.message_to_uid = r.id
join (select if(message_from_uid = $YOUR_USER_ID, message_to_uid, message_from_uid) as user_id_other,
max(message_time) AS date_time_max from YOUR_MESSAGES_TABLE
where (message_from_uid = $YOUR_USER_ID OR message_to_uid = $YOUR_USER_ID)
group by if(message_from_uid=$YOUR_USER_ID, message_to_uid, message_from_uid)
) AS t
on if(m.message_from_uid = $UID, m.message_to_uid, m.message_from_uid) = user_id_other
AND m.message_time=date_time_max
where m.message_from_uid = $UID OR m.message_to_uid = $UID
order by m.message_time desc limit 5
Share solution ↓
Additional Information:
Link To Answer People are also looking for solutions of the problem: using $this when not in object context laravel
Didn't find the answer?
Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.
Similar questions
Find the answer in similar questions on our website.
Write quick answer
Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.