Mysql – Trying SQL query with subquery in WHERE clause

MySQLsubquery

For a message system we would like to know if a conversation allready exists in the database. The user will then be posting to the existing conversation instead of a new one.

Now, we have linked all participants to conversations. So on creating a new one, we count the number of people marked as participants (included current user (:totalUserCount)), en then would like a query to check if a conversation with x participants and all marked user(name)s exist before creating a new conversation.

After a few attempts we have a query that executes, but the result is not correct.

SELECT `app_company_user_cons`.* 
FROM (`app_company_user_cons`) 
LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_user_cons`.`id` = `link_company_user_cons_company_users`.`company_user_conversation_id` 
LEFT OUTER JOIN `app_company_users` app_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 

WHERE :totalUserCount = (SELECT 
    COUNT(*) 
    FROM (`app_company_users`) 
    LEFT OUTER JOIN `link_company_user_cons_company_users` link_company_user_cons_company_users ON `app_company_users`.`id` = `link_company_user_cons_company_users`.`company_user_id` 
    WHERE link_company_user_cons_company_users.company_user_conversation_id = app_company_user_cons.id 
) 
AND `link_company_user_cons_company_users`.`company_user_id` =  :currentUserID 
AND `app_company_users`.`name` =  :otherUserID2
AND `app_company_users`.`name` =  :otherUserID3
AND `app_company_users`.`name` =  :otherUserIDn 
LIMIT 1

The EXPLAIN function shows me: 

+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| id | select_type        | table                                | type   | possible_keys | key     | key_len | ref                                                               | rows | Extra                                               |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 1  | PRIMARY            | NULL                                 | NULL   | NULL          | NULL    | NULL    | NULL                                                              | NULL | Impossible WHERE noticed after reading const tables |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | link_company_user_cons_company_users | ALL    | NULL          | NULL    | NULL    | NULL                                                              | 2    | Using where                                         |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+
| 2  | DEPENDENT SUBQUERY | app_company_users                    | eq_ref | PRIMARY       | PRIMARY | 4       | de1210mo_CRM.link_company_user_cons_company_users.company_user_id | 1    | Using where; Using index                            |  |
+----+--------------------+--------------------------------------+--------+---------------+---------+---------+-------------------------------------------------------------------+------+-----------------------------------------------------+--+

Not sure if we are missing something. Any ideas on how to solve this?
Thanks in advance!

(PS: Our app adds AND 'app_company_users'.'name' = for all participants in an array)

Best Answer

If mluc is a many:many mapping between users and conversations, then consider

SELECT conversation_id,
       GROUP_CONCAT(user_id ORDER BY user_id) AS participants
    FROM mluc
    HAVING participants = :list

Where you build the string of the ordered list of user_ids separated by commas.

This requires a scan of mluc, so it may not be the best possible, but I think it will be faster than generating an arbitrary list of EXISTS clauses.

This gives tips on building an optimal many:many table. Some of the tips will help my GROUP_CONCAT technique run faster.

Related Question