I have a hard time solving this little query riddle.

I have 3 tables: users, message_threads and message_thread_users.

I am looking for a query where I can look up the right message_thread by providing the permalinks of two or more users.

In the following structure example an example could be that I want the message_thread with id=1 returned when looking up the two user permalinks for user John and user Phyllis. Both of these users also appear in message_thread with id=2 but in this message_thread, Boris is also a message_thread_user, so there is only one right match here. The same should happen if I look up the matching message_thread for all three user permalinks and the message_thread with id=2 should return correctly this time.

I'm looking for a solution possible in MySQL and PHP and maybe with some inline-coded array-solution? Like this:

...
mtu.user_id IN (SELECT u.id FROM users u WHERE u.permalink IN ("' . implode('","', $permalinks) . '")) AND
...

But I still need some guidance to the correct joining and the final query. I hope you understand the issue / challenge. Please let me know what you think!

users:

message_threads:

message_thread_users:

CodePudding user response：

select *
  from message_threads mt
 where <n> = (select count(1) 
                from message_thread_users mtu 
               where mtu.message_thread_id = mt.id
                 mtu.user_id in <users>)

Here <n> and <users> will be bind variables.

CodePudding user response：

I solved the query like this:

'SELECT
    mt.permalink as "message_thread_permalink"
 FROM
    message_threads mt
    LEFT JOIN message_thread_users mtu ON mtu.message_thread_id = mt.id 
    LEFT JOIN users u ON u.id = mtu.user_id 
WHERE
    u.permalink IN ("' . implode('","', $permalinks) . '") 
GROUP BY
    mt.id
HAVING
    COUNT(DISTINCT u.id) = ' . count($permalinks);

The permalinks are of course "cleaned" for potential threads when used like this.