Mysql test if 2 users have a conversation

Question

I am making a message system, much like on facebook. When a user send a new message to a person from their profile, instead of from within messages, i want to check the database if they already have a conversation together.

My tables look like:

messages =>
  m_id (message id)
  t_id (thread id)
  author_id
  text

thread_recipients =>
  t_id (thread id)
  user_id (id of the user belonging to the thread/conversation)
  is_read

So basically i have a row for each user belonging to a conversation, and every message has a thread that it belongs to.

So lets say i have user_id 14 and the user im writing to has 16. Then i would need to find out if these rows existed:

t_id    user_id    is_read
 x         16         1
 x         14         1

The thread id's would have to match, and there should not be any other users in that thread.

Can this be done in one query?

Menelaos · Answer 1 · 2013-05-19T10:53:10.680

3

You could do a unary join of thread recipients to itself and then use where.

SELECT tr1.*,
       tr2.*
FROM thread_recipients tr1,
     thread_recpipients tr2
WHERE tr1.t_id = tr2.t_id
  AND tr1.user_id = WRITER_ID
  AND tr2.user_id = RECIPIENT_ID;

If you want to have the count just replace

tr1.*,tr2.*

with

count(*)

If you want to remove threads that have other users as well you can try Bohemian's solution (which I haven't tested but suspect is most efficient) or this:

SELECT tr1.*,
       tr2.*
FROM thread_recipients tr1,
     thread_recpipients tr2
WHERE tr1.t_id = tr2.t_id
  AND tr1.user_id = WRITER_ID
  AND tr2.user_id = RECIPIENT_ID AND
  NOT EXISTS(select t_id from thread_recipients where user_id not in (WRITER_ID, RECIPIENT_ID) limit 1);

edited May 19 '13 at 10:53

answered May 19 '13 at 10:19

Menelaos

23,508
18
90
155

Hello Bohemian..always a pleasure to see your comments :) . Quite informative! http://stackoverflow.com/questions/121631/inner-join-vs-where – Menelaos May 19 '13 at 10:35
So if i just did SELECT tr1.t_id it would return the thread_id, that only those 2 share? – jah May 19 '13 at 10:44
@Jens Ahlsten Herlevsen Yes, this is the case. – Menelaos May 19 '13 at 10:45
Actually, no, not the case. This query will not exclude conversations that other users participated in (eg a 3-way) – Bohemian May 19 '13 at 10:47
Oops, missed that part due to the grammatical error in original question. Updating to be relevant... – Menelaos May 19 '13 at 10:50
@meewoK I far prefer join syntax vs where its far more readable especially when many other where statements and its a reason its the general standard for queries. Yea the query optimizer will perform the query the same both ways but far easier to read multi joins imo – wired00 May 19 '13 at 11:19
@wired00 True. I will update accordingly. My only issue is with people embellishing their comments/answers with non-constructive irony/sarcasm. Though they always later go on and deny it, it's quite obvious. – Menelaos May 19 '13 at 11:44

score 1 · Answer 2 · answered May 19 '13 at 10:22

1

Here is query that can get you the number of rows. So you can check if it is 2.

select 
    count(*) 
from 
    thread_recepients tr1  
inner join 
     thread_recepients tr2 
on 
     tr1.t_id = tr2.t_id 
where 
     (tr1.user_id = 'someuderid' or tr2.user_id = 'theotherguy')

answered May 19 '13 at 10:22

CodeTower

6,293
5
30
54

Including `tr1.user_id != tr2.user_id` is redundant. They cannot be the same because of the first condition in where. It's quite unlikely OP will check for messages a user sent to himself. – Menelaos May 19 '13 at 10:26

Bohemian · Accepted Answer · 2013-05-19T11:48:07.773

1

Join the table to itself thrice:

select tr1.t_id 
from thread_recepients tr1  
join thread_recepients tr2 on tr2.t_id = tr1.t_id 
    and tr2.user_id = 16
left join thread_recepients tr3 on on tr3.t_id = tr1.t_id
    and tr3.user_id not in (14, 16)
where tr1.user_id = 14
and tr3.user_id is null

The is null test asserts no other users participated (no other rows joined) in the conversation, as per your request:

can not be any other users belonging to that thread

because we want the left joined rows for other users to not be found.

Recommended indexes:

create index thread_recepients_t_id on thread_recepients (t_id);
create index thread_recepients_user_id on thread_recepients (user_id);

edited May 19 '13 at 11:48

answered May 19 '13 at 10:45

Bohemian

412,405
93
575
722

I get the error column 'user_id' is ambigious. Should it be tr3.user_id or tr1.user_id/tr2.user_id? – jah May 19 '13 at 11:03
Yes, it was meant to be `tr3.user_id` - I left that out by mistake. Answer edited - try it now. – Bohemian May 19 '13 at 11:10
That works perfectly. Thankyou! As of now i have the primary key of the thread_recipients table on (t_id, user_id). Do you agree, that should be enough for indexing? – jah May 19 '13 at 11:22
How can this be repurposed for n users ? – Yannick Y Feb 14 '20 at 02:42
@YannickY do you want to find items that *all* of a group of certain users participated in, or items that *only* (but not necessarily *all*) of a group of certain users participated in. – Bohemian Feb 14 '20 at 03:15

Mysql test if 2 users have a conversation

3 Answers3