For this type of scenarios, I would better suggest a different type of database structure to create a message thread with all the users involved and instead of connecting each messages to each users, connect them to the threads. Here are the sample tables:
MessageThreads
| thread_id | created_at |
-----------------------------------
| 1 | 2016-01-20 18:24:36 |
| 2 | 2016-01-20 19:24:24 |
ThreadRecipients
| thread_id | user_id | last_read_message |
-----------------------------------------------
| 1 | 1 | 2 |
| 1 | 2 | 3 |
| 1 | 3 | 1 |
ChatMessages (like before)
| message_id | from_id | chat_text | chat_datetime |
---------------------------------------------------------------------
| 1 | 1 | Test | 2016-01-20 18:24:36 |
| 1 | 1 | Test2 | 2016-01-20 19:24:36 |
| 1 | 2 | Test3 | 2016-01-20 19:34:36 |
ThreadMessages
| thread_id | message_id |
---------------------------
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
Here, inplace of isRead field in your ChatRecipients table, I have used last_read_message in ThreadRecipients table, where you can always update with the last seen message of the user in the thread. But if you want to still keep a such track of each message to each user, you can still have another table with only message_id and user_id where data will be inserted only if the message is read by the user. (And You can still use your ChatRecipients table for one to one messages, if you don't want to create thread in that case. )
Why It's necessary
It's because if you use ChatRecipients table you are adding multiple rows to ChatRecipients table for each message, in the long run its going to cost you some extra space. But if you use ThreadMessages as I suggested, you will put only one row per message in ThreadMessages and users will be connected to threads through ThreadRecipients table which will be one row per user per thread.
For example, if you have thread of 100 users with 50 messages, in your approach you will have 50 x 100 rows in ChatRecipients table. But with this approach, it will be 100 rows in ThreadRecipients table and 50 rows in ThreadMessages table. Just think about the difference.
How to Insert Data
So, when you have a new message thread between a group of persons. As for your example, we have three users with ID 1, 2, 3.
- Insert a new thread to
ThreadRecipients table. Get the new thread_id. (It can be an auto incremented value)
Now for each associated user_id, insert one row in ThreadRecipients table. For example, We have thread_id 3 and user_id 1, 2 , 3.
INSERT INTO ThreadRecipients (thread_id, user_id) VALUES(3, 1), (3, 2), (3, 3)
Now, when any person send messages to the thread, just insert the row to ChatMessages table (like before), get the message_id and Insert a new row to ThreadMessages with thread_id and message_id. For example our message_id = 9.
INSERT INTO ThreadMessages (thread_id, message_id) VALUES(3, 9)
When anyone reads the message, just update the last_read_message for the user in the ThreadRecipients table with the read message_id (the condition last_read_message < 3 makes sure that, the message you are updating with isn't older than the existing last_read_message).
UPDATE ThreadRecipients SET last_read_message = 3 WHERE user_id = 2 AND thread_id = 3 AND last_read_message < 3
Note: Always before inserting new thread, check if a thread already exists with the same users so that you don't have duplicate thread for the same group of users. (See below for how to find existing thread for specific users).
How to Get Messages
Now, your query should only check if there is a thread involving the specific users and no other users are involved in the thread. So, in WHERE clause
First we have a sub query SELECT COUNT(*) FROM ThreadRecipients WHERE user_id in ('1', '2', '3') AND thread_id = tm.thread_id) and we are checking if that equals 3. It will be 4, if the number of users is 4 and so on. (Keep a UNIQUE key for thread_id + user_id, so that there can never be a data duplication and get an incorrect count match thereby).
The other condition makes sure, there is no other user involved so we are just checking if any row exists WHERE NOT user_id IN ('1', '2', '3') AND thread_id = tm.thread_id). If exists, we will just consider it as another thread involving more persons.
So, finally the query can be like this : (See the SQL Fiddle)
SELECT
cm.message_id as 'message_id',
cm.from_id as 'from_id',
(SELECT u.user_fname as 'fname' from Users u where u.user_id = cm.from_id) as 'firstName',
(SELECT u.user_lname as 'lname' from Users u where u.user_id = cm.from_id) as 'lastName',
cm.chat_text as 'chat_text'
FROM
ChatMessages cm
INNER JOIN
ThreadMessages tm
ON
cm.message_id = tm.message_id
INNER JOIN
Users u
ON
cm.from_id = u.user_id
WHERE
(SELECT COUNT(*) FROM ThreadRecipients WHERE user_id in ('1', '2', '3') AND thread_id = tm.thread_id) = 3
AND NOT EXISTS(select NULL FROM ThreadRecipients WHERE NOT user_id IN ('1', '2', '3') AND thread_id = tm.thread_id)
