CODEWITHSUNDEEP
mysqlsqlormeloquentquery-builder
S4L4H
S4L4H

Reputation: 452

Message discussion query

I try to create a discussion system that display messages between two users ordering by time like the image using SQL, for example if user 10 want to see his messages with user 45, this is my table structure:

Messages( id(PK), sender_id(FK_USER), receiver_id(FK_USER), subject, content,created_at,updated_at);

I tried this sql query but it doesn't show what i want:

select s.id,
       s.subjet,
       d.source_id as `source`,
       d.id,
       d.subject 
 from messages s,
      messages d
where s.destination_id=d.source_id 
  and d.source_id=s.destination_id 
  and s.source_id=202 
order by created_at asc;

Example

Upvotes: 0

Views: 48

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133370

I think you need a select case (i have leaved all the column for demo) 

 select s.id, 
    case s.sender_id when 202 then s.subject else '' end, 
    case s.receicer_id when 202 then s.subject else '' end, 
    s.subject, s.content, s.created_at
from message s
where s.sender_id = 202
order by s.created_at ASC

Upvotes: 1

pratik garg
pratik garg

Reputation: 3342

If I understand your table structure and requirement correctly, you can try something like below - 

SELECT *
  FROM MESSAGES
 WHERE SENDER_ID IN (10, 45)
   AND RECEIVER_ID IN (10, 45)
 ORDER BY CREATED_AT

Or like this -

SELECT *
  FROM MESSAGES
 WHERE (SENDER_ID = 10 AND RECEIVER_ID = 45)
    OR (RECEIVER_ID = 10 AND SENDER_ID = 45)
 ORDER BY CREATED_AT

by default ordering will be done in ASC fashion so no need to mention that.

Upvotes: 1

Related Questions