CODEWITHSUNDEEP
sql
tirenweb
tirenweb

Reputation: 31729

Users and Messages: how to order by Sender, Receiver or Both?

I have two tables like these:

USER
id
name

MESSAGE
id
sender_id
receiver_id

As you suppose, there are two one-to-many relations between those tables.

In my backend, I have filter to search for the messages associated to a user. After pressing "Filter", I should show the list of messages associated to him/she, showing a column that specifies if the person was the sender or the receiver (or even both) of the message in a column called "Sender/Receiver".

My doubt: I have also to give the possibility to order the list in base of the "Sender/Receiver" column. You know, pressing on "Sender/Receiver" column header will show first the messages where the user is "Sender", then where is "Both" and finally where is "Receiver". And pressing again will show the messages where the user is "Receiver", then where is "Both" and then where is "Sender".

What kind of SQL can do that ordering?

NOTE: the schema I proposed is not mandatory. If it is necessary propose another one.

Upvotes: 0

Views: 617

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

You just need a fancy order by statement:

select m.*
from message m join
     user s
     on u.sender_id = s.id join
     user r
     on u.receive_id = r.id
where s.id = <user> or r.id = <user>
order by (case when s.id = <user> and r.id <> <user> then 0
               when s.id = <user> and r.id = <user> then 1
               when r.id = <user> then 2
               else 3 -- shouldn't happen
         )

The order by looks at the user you are interested in for the sender and receiver, and then does some logic to determine the order according to your constraints (send-nly first, then both, then receiver).

Upvotes: 0

ruakh
ruakh

Reputation: 183456

ORDER
   BY CASE WHEN sender_id = [user-ID of interest]
            AND receiver_id = [user-ID of interest]
           THEN 2      -- is both sender & receiver
           WHEN sender_id = [user-ID of interest]
           THEN 1      -- is only sender
           ELSE 3      -- is only receiver
       END,
       [other conditions to sort by]

Upvotes: 1

Related Questions