1

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)

  • Please provide SHOW CREATE TABLE. – Rick James Nov 13 '16 at 18:04
  • Solved it .. see the answer below. – Brainfeeder Nov 13 '16 at 18:06
0

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.

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

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 :)

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.