mysql - 检查线程是否已存在于群组对话中

Question

我有一个用户发送消息的平台。群组对话是可能的。

我的 thread_members 表:

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  

当我发送消息时，我想检查这个线程是否已经存在。

假设:user_id '1' 向 user_id '2' 发送消息

结果应该是thread_id '1'

应该是单个查询

编辑

感谢大家，但我想我得到了答案:到目前为止，我没有遇到任何问题...

在 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)都是接收者+发送者

Answer 1

给你:

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);
        }
    }
}

结果:
用户 1 发送线程 1 用户 2
用户 1 发送线程 2 用户 2
用户 1 发送线程 2 用户 3
用户 1 发送线程 3 用户 3
用户 1 发送线程 3 用户 4
用户 2 发送线程 1 用户 1
用户 2 发送线程 2 用户 1
用户 2 发送线程 2 用户 3
用户 3 发送线程 2 用户 1
用户 3 发送线程 2 用户 2
用户 3 发送线程 3 用户 1
用户 3 发送线程 3 用户 4
用户 4 发送线程 3 用户 1
用户 4 发送线程 3 用户 3


希望这对您有所帮助。