SQL – Selecting a users conversation based on members

I am trying to formulate a query to select a conversation based on members passed in, this can be many participants.

Users can have multiple conversation with the same users in, and they can rename conversations.

Here is my messages members table:

CREATE TABLE `messages_members` (
      `relation_id` int(11) NOT NULL,
      `user_id` int(11) NOT NULL,
      `date` datetime NOT NULL,
      `seen` smallint(6) NOT NULL,
      PRIMARY KEY (`relation_id`,`user_id`),
      KEY `seen` (`seen`,`date`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

And the messages relation table:

CREATE TABLE `messages_relation` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sender_id` int(11) NOT NULL,
  `date` datetime NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Messages table for reference:

CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `relation_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `message` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `updated` smallint(6) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `messages` (`relation_id`,`user_id`),
  KEY `date` (`date`)
) ENGINE=InnoDB AUTO_INCREMENT=135 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

My latest solution is not working right as it is selecting a wrong conversation based on members count passed in.

Only thing I can think of at the momemt is to take the limit off the query and then loop through each result and do some checks to see if the members match what was asked for.

I wonder if there is a more elegant solution using a query though to do this.

Here is my current query: EDIT: Updated query as was doing the count properly.

  SELECT relation_id AS relation, GROUP_CONCAT(user_id) AS members, COUNT(user_id) AS selected_members,
            (SELECT COUNT(user_id) FROM messages_members WHERE relation_id = relation) AS total_members
            FROM messages_members            
            WHERE user_id IN( ". $inQuery ." )
            GROUP BY relation_id 
            HAVING total_members = ? AND selected_members = ? 

I am passing in each users_id from an array, and also the total count of the array to match total members.

For more clarity to try explain what im trying to do there could be multiple conversations with simialir participants:

1. Conversation A : user1, user2, user3
2. Conversation B : user1, user2
3. Conversation C : user1, user2, user5

I am trying to just select conversation A based on the three users passed in, user1, user2, user3

Some sample data from the messages_members table: relation_id, user_id, date, seen

relation_id	user_id	date	seen
2	1	2020-11-18 19:16:54.000	0
5	4	2020-11-18 19:53:34.000	0
5	6	2020-11-18 19:53:34.000	0
14	1	2020-11-19 00:02:44.000	0
14	3	2020-11-19 00:02:44.000	0
19	1	2020-11-19 00:16:32.000	0
19	3	2020-11-19 00:16:32.000	0
20	3	2020-11-19 00:17:37.000	0
20	4	2020-11-19 00:17:37.000	0
21	1	2020-11-19 00:18:09.000	0
21	3	2020-11-19 00:18:09.000	0
21	6	2020-11-19 00:18:09.000	0
22	1	2020-11-19 00:18:45.000	0
22	4	2020-11-19 00:18:45.000	0
22	6	2020-11-19 00:18:45.000	0
23	1	2020-11-19 00:19:06.000	0
23	3	2020-11-19 00:19:06.000	0
23	4	2020-11-19 00:19:06.000	0
24	3	2020-11-19 00:19:42.000	0
24	4	2020-11-19 00:19:42.000	0
24	6	2020-11-19 00:19:42.000	0
25	3	2020-11-19 01:41:44.000	0
25	5	2020-11-19 01:41:44.000	0
43	1	2022-02-28 17:38:34.000	0
43	54	2022-02-28 17:38:35.000	0
46	1	2022-03-16 23:24:43.000	0
46	5	2022-03-16 23:24:43.000	0
47	1	2022-03-16 23:25:51.000	0
47	3	2022-03-16 23:25:51.000	0
47	5	2022-03-16 23:25:51.000	0
48	1	2022-03-17 00:19:26.000	0
2	5	2020-11-18 19:16:54.000	1
23	6	2020-11-19 00:19:06.000	1
47	6	2022-03-16 23:25:51.000	1
48	15	2022-03-17 00:19:26.000	1
54	3	2022-03-19 00:19:22.000	1
54	5	2022-03-19 00:19:22.000	1
55	1	2022-03-19 00:23:18.000	1
55	3	2022-03-19 00:23:18.000	1
55	5	2022-03-19 00:23:18.000	1

For some examples using the above data, relation_id is one conversation.

So using the bottom 3 examples,

1. If i want to look for a conversation just between user 1 and user 3, it should not return relation_id 55, because it also has user 5 in it.
2. Same as relation_id 47 as that has user 5 in it too.
3. It should just return relation_id 14 which only has user1, and user3. There is more conversations just between user1 and user3 but I would just return the first one it finds.

Answer

I’m not a php guy, but something like this would return only conversations involving user_id 1 and user_id 3:

SELECT mm.relation_id
       , GROUP_CONCAT(mm.user_id) AS User_List
FROM   message_members mm
WHERE  mm.user_id IN (1,3) -- find user_id 1 or 3
AND    NOT EXISTS (
         -- Does not involve other user_id's
         SELECT NULL
         FROM   message_members ex
         WHERE  ex.relation_id = mm.relation_id
         AND    ex.user_id NOT IN (1,3) -- exclude user_id 1 or 3
)
GROUP BY mm.relation_id
-- Where "2" is the unique number of users 
-- i.e. Find user 1 and user 2 = 2 distinct user_id's
HAVING COUNT(DISTINCT mm.user_id) = 2
;

Results:

relation_id | User_List
----------: | :--------
         14 | 1,3      
         19 | 1,3      

db<>fiddle here