CODEWITHSUNDEEP
phpmysqlsql
algorithmicCoder
algorithmicCoder

Reputation: 6789

Ordering user's messages SQL

I am trying to build a user's message inbox by grouping conversations by people the user has sent messages to.

So I want a list of users but i want the first user in the list to be the one who sent the most recent message.

My message table has the fields: id, sender_id, receiver_id, text

And the following query gives me the list i need but doesn't order them by in descending order of message id.

SELECT sender_id AS messgr_id FROM   messages WHERE  receiver_id = '$sess_id' 
UNION
SELECT receiver_id AS messgr_id FROM messages  WHERE  sender_id = '$sess_id'

I am hoping there is a way to order the results in SQL without having to use a potentially slower php process.

Thanks!

Upvotes: 0

Views: 93

Answers (2)

Blessed Geek
Blessed Geek

Reputation: 21664

Just as Zeus says, why can't you have a datetime stamp for the messages?

A messaging system without datetime stamp is not a messaging system.

The datetime stamp is the natural means to sort your messages.

Upvotes: 0

krock
krock

Reputation: 29619

Assuming that id is an identity column (specifically you will want it to be auto incrementing) then a larger id indicates that a record is more recent. You will be able to sort by id in this case in descending order to get newer messages first. Something like this (untested):

SELECT id,
       CASE WHEN receiver_id = '$sess_id' THEN sender_id
            ELSE receiver_id END AS messgr_id
FROM messages
WHERE  receiver_id = '$sess_id'
OR sender_id = '$sess_id'
ORDER BY id DESC

Upvotes: 1

Related Questions