Check if a thread exists in a group conversation
I have a platform where users post messages. Group conversation is possible.
My thread_members table:
id | user_id | thread_id
-------------------------
1 | 1 | 1
-------------------------
2 | 2 | 1
-------------------------
3 | 1 | 2
-------------------------
4 | 2 | 2
-------------------------
5 | 3 | 2
-------------------------
6 | 1 | 3
-------------------------
7 | 3 | 3
-------------------------
8 | 4 | 3
When I post, I want to check if this thread exists.
Let's say: user_id '1' sends message to user_id '2'
Result should be thread_id '1'
This should be one request
EDIT
Thanks everyone, but I think I got my answer: I am not getting any problems so far ...
In PHP:
$senderId = $_POST['sender']; // integer
$receiverIds = $_POST['receivers']; // array
$query = "SELECT thread_members.*, COUNT(*)
FROM thread_members
JOIN (
SELECT *, COUNT(*)
FROM (
SELECT *
FROM thread_members
WHERE user_id = " . $senderId . " ";
foreach ($receiverIds as $receiverId)
{
$query .= " OR user_id = " . $receiverId . " ";
}
$query .= " ) AS thread_members
GROUP BY thread_id
HAVING COUNT(*) = " . (count($receiverIds) + 1) . "
ORDER BY COUNT(*)
) AS thread_members_copy ON thread_members.thread_id = thread_members_copy.thread_id
GROUP BY thread_members.thread_id
HAVING COUNT(*) = " . (count($receiverIds) + 1) . "
ORDER BY COUNT(*) ";
(count($receiverIds) + 1)
- all recipients + sender
source to share
Here you are:
void Main()
{
var data = new List<Tuple<int, int, int>>{
new Tuple<int,int,int>(1,1,1),
new Tuple<int,int,int>(2,2,1),
new Tuple<int,int,int>(3,1,2),
new Tuple<int,int,int>(4,2,2),
new Tuple<int,int,int>(5,3,2),
new Tuple<int,int,int>(6,1,3),
new Tuple<int,int,int>(7,3,3),
new Tuple<int,int,int>(8,4,3),
};
var users = data.Select(x => x.Item2).Distinct();
foreach(var user in users){
var threads = data.Where(x => x.Item2 == user).Select(x => x.Item3);
foreach(var thread in threads){
var counterParties = data.Where(x => x.Item3 == thread && x.Item2 != user)
.Select(x => x.Item2);
foreach(var party in counterParties)
Console.WriteLine("user {0} send thread {1} user {2}", user, thread, party);
}
}
}
Result:
user 1 send thread 1 user 2
user 1 send thread 2 user 2
user 1 send thread 2 user 3
user 1 send thread 3 user 3
user 1 send thread 3 user 4
user 2 send thread 1 user 1
user 2 send thread 2 user 1
user 2 send thread 2 user 3
user 3 send thread 2 user 1
user 3 send thread 2 user 2
user 3 send thread 3 user 1
user 3 send thread 3 user 4
user 4 send thread 3 user 1
user 4 send thread 3 user 3
Hope it helps.
source to share