CODEWITHSUNDEEP
mysqlgroup-by
Andy Joe
Andy Joe

Reputation: 3

MYSQL : Unique Message Threads

I have a messages table with columns like

message_id, message_to_id, message_from_id, message_body, project_id.

I want a list of message threads for a particular user. Message thread should have a unique combination of fields message_to_id, message_from_id and project_id.

Earlier I was using group by on project_id but I don't want to make them group message over project.

SELECT * FROM message
WHERE message_to = '{$user_id}' OR message_from = '{$user_id}'
GROUP BY project

This didn't work as expected.

SELECT * 
FROM message 
WHERE message_to = '{$user_id}' 
GROUP BY message_from 
UNION 
SELECT * 
FROM message 
WHERE message_from = '{$user_id}' 
GROUP BY message_to

This is showing me combinations of message_from and message_to which I don't want.

I want a message thread per user combination on a single project.

Sample Data

message_id | message_from_id | message_to_id | project_id | message _body
1          |  283            |  284          | 4          | Hello         
2          |  284            |  283          | 4          | Hi            
3          |  285            |  283          | 4          | Hey there!    
4          |  283            |  285          | 4          | Greetings     
5          |  283            |  284          | 6          | Cool!

What I want

message_id | message_from_id | message_to_id | project_id | message _body 
1          |  283            |  284          | 4          | Hello                
3          |  285            |  283          | 4          | Hey there!  
5          |  283            |  284          | 6          | Cool!

i.e ignore message_from_id and message_to_id ordering but together with project_id they should be unique.

Upvotes: 0

Views: 78

Answers (2)

James Scott
James Scott

Reputation: 1084

I came up with a completely different solutions using an ON or ranking approach (ROW_NUMBER or DENSE_RANK) although this wasn't compatible with all SQL versions, looking around I found a JOIN approach that appears to do the job.

Here is my result:

SELECT message.message_id, message_to_id, message.message_from_id, message.project_id, message.message_body FROM 
  message
INNER JOIN
  (
  SELECT MIN(message_id) message_idMIN FROM message WHERE message_to_id = 283 GROUP BY project_id, message_to_id
    UNION 
  SELECT MIN(message_id) message_idMIN FROM message WHERE message_from_id = 283 GROUP BY project_id, message_from_id
  ) message_group
ON message.message_id = message_group.message_idMIN;

See the fiddle here: http://sqlfiddle.com/#!9/ef36a1/32

Upvotes: 0

Andy Joe
Andy Joe

Reputation: 3

The correct answer is to ignore the combination of message_to and message_from and give distinct of the same.

SELECT DISTINCT 
    CASE WHEN message_from_id < message_to_id 
    THEN message_from_id 
    ELSE message_to_id END as message_from_id, 
    CASE WHEN message_from_id < message_to_id 
    THEN message_to_id 
    ELSE message_from_id END as message_to_id, 
    project 
FROM message 
WHERE message_to_id = '{$user_id}' OR message_from_id = '{$user_id}'

This way it can work without Group By Clause.

But the problem with this solution is I can't get any non-distinct columns like sent_at (which I didn't mention here).

Upvotes: 0

Related Questions