Trying SQL query with subquery in WHERE clause

Question

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)

• Rick James: Please provide SHOW CREATE TABLE.
• Brainfeeder: Solved it .. see the answer below.

Answer 1

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.

• Brainfeeder: So the question mark is the string with user_ids to check? I'll look into this :) thx
• Rick James: Yeah, the ? is a placeholder -- different client software does it different ways. Ah, I should have noticed the : convention. I edited my answer.

Answer 2

We have a solution, no idea if it is legit ^^

SELECT 
 muc . * , 
 (
 SELECT COUNT( * )
 FROM users AS u
 LEFT OUTER JOIN link_users_conversations luc ON u.id = luc.user_id
 WHERE luc.user_conversation_id = muc.id
 ) readers
FROM user_conversations AS muc
LEFT OUTER JOIN link_users_conversations mluc ON muc.id = mluc.user_conversation_id
WHERE mluc.user_id = :currentUserID
/* Check for all 'readers' if a relation exists */
AND EXISTS (
 SELECT *
 FROM link_users_conversations AS c0
 WHERE c0.user_id = :participantID0
 AND c0.user_conversation_id = muc.id
)
AND EXISTS (
 SELECT *
 FROM link_users_conversations AS cn
 WHERE cn.user_id = :participantIDn
 AND cn.user_conversation_id = muc.id
)
HAVING readers = :totalReaders /* Total readers could be higher while all EXISTS pass. */
LIMIT 1 

I simplified names in this example.

So it mimics behaviour as seen at Facebook when starting a new Chat conversation. It will search the database if any other conversation has exactly (EXACTLY!) the same participants/readers/followers, if one exists, the new message will be posted to the existing conversation instead of creating a new one.

(FYI: The SQL provided is to check for existing conversations only. Creating messages and new conversations need other functions.)

If someone knows of a better way to do this check, please comment :)

• Rick James: Does this mean you are generating N exists clauses? Grossly inefficient.
• Brainfeeder: Yes.. for now n could go up to 10. If another solution exists I would love some tips :)