CODEWITHSUNDEEP
mysqlsql
Philipp Mochine
Philipp Mochine

Reputation: 4705

(My)SQL - For showing last Inbox of Messages

So hopefully the question is not asked, but I didn't find it.

My example table looks like this: https://i.sstatic.net/dxiFs.jpg

For my SQL I know for example the user ID of 1 and I want to show the Inbox of my Messanger for that I need to know if User ID 1 received a message or was the sender of a message.

For example:

sender_id receiver_id
  1         2
  3         1
  1         4
 12         1

Should give me four Inbox results for ID 1.

So I currently stuck with 

SELECT * FROM chats AS tm 
WHERE tm.id IN
(SELECT MAX(id) FROM chats WHERE sender_id = 1 OR receiver_id = 1 GROUP BY receiver_id)

This gets me: https://i.sstatic.net/8rxGy.jpg

You'll see that I don't need ID 2, but I care for ID 3, because it's the latest message of that conversation.

So how can I decide with SQL that I want the latest of sender_id 1 or receiver_id 1?

Thank you!

Upvotes: 0

Views: 48

Answers (2)

Willas
Willas

Reputation: 63

SELECT * FROM chats AS tm WHERE tm.id IN (SELECT MAX(id) FROM chats WHERE sender_id = 1 OR receiver_id = 1 GROUP BY receiver_id)

You could try with Top(1) like this

SELECT id FROM chats WHERE sender_id = 1 OR receiver_id = 1 ORDER BY ID DESC
LIMIT 1

This is the last row in chats witch contains a sender or reciver with id 1

Is a first a aproximation (It´s suboptimal)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

One option uses a least/greatest trick:

SELECT t1.*
FROM chats t1
INNER JOIN
(
    SELECT
        LEAST(sender_id, receiver_id) AS sender_id,
        GREATEST(sender_id, receiver_id) AS receiver_id,
        MAX(updated_at) AS max_updated_at
    FROM chats
    GROUP BY
        LEAST(sender_id, receiver_id),
        GREATEST(sender_id, receiver_id)
) t2
    ON LEAST(t1.sender_id, t1.receiver_id) = t2.sender_id AND
       GREATEST(t1.sender_id, t1.receiver_id) = t2.receiver_id AND
       t1.updated_at = t2.max_updated_at;

enter image description here

Demo

Upvotes: 1

Related Questions