1

I know there are similarly questions already asked like this one. But they don't seem to work for me.

What I am trying to do:

I am trying to get the last message of each contact of a given user.

Problem

Personal Message Table:

+-----------+---------------------+---------------------+-------------+
| sender_id | text                | created_date        | receiver_id |
+-----------+---------------------+---------------------+-------------+
|        16 | hello               | 2020-11-22 22:37:40 |          15 |
|        15 | hi                  | 2020-11-22 22:37:55 |          16 |
|        15 | how are you ?       | 2020-11-22 22:38:18 |          16 |
|        18 | hey                 | 2020-11-22 22:40:12 |          16 |
|        16 | you there ?         | 2020-11-22 22:40:32 |          18 |
|        15 | where are you ?     | 2020-11-22 23:50:23 |          18 |
|        15 | can we talk         | 2020-11-22 23:50:38 |          18 |
|        18 | how is life ?       | 2020-11-22 23:53:32 |          15 |
|        18 | whats up            | 2020-11-22 23:54:38 |          15 |
+-----------+---------------------+---------------------+-------------+

With the above table we can see the user with id 15 has had conversation with user 16 and 18. What I want is the last message of each conversation so for conversation between user 15 and 16 the last message would be:

+-----------+---------------------+---------------------+-------------+
| sender_id | text                | created_date        | receiver_id |
+-----------+---------------------+---------------------+-------------+
|        15 | how are you ?       | 2020-11-22 22:38:18 |          16 |
+-----------+---------------------+---------------------+-------------+

similarly last message of conversation between 15 and 18 will be:

+-----------+---------------------+---------------------+-------------+
| sender_id | text                | created_date        | receiver_id |
+-----------+---------------------+---------------------+-------------+
|        18 | whats up            | 2020-11-22 23:54:38 |          15 |
+-----------+---------------------+---------------------+-------------+

My query so far:

select * from personal_message where receiver_id in 
   (
     select receiver_id from personal_message where receiver_id in 
       (
         select receiver_id from 
         personal_message where receiver_id = 15
       ) order by created_date desc
   ) group by sender_id;

I have tried many queries which I don't even remember.

Ahtisham
  • 9,170
  • 4
  • 43
  • 57

1 Answers1

1

On MySQL 8+, the ROW_NUMBER window function can be used here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY LEAST(sender_id, receiver_id),
                                              GREATEST(sender_id, receiver_id)
                                 ORDER BY created_date DESC) rn
    FROM personal_message
    WHERE sender_id = 15 OR receiver_id = 15
)

SELECT sender_id, text, created_date, receiver_id
FROM cte
WHERE rn = 1;

The partition used in ROW_NUMBER above will always ensure that either the sender or receiver be user 15.

A version using joins, which is more amenable to earlier versions of MySQL or ORM tools:

SELECT pm1.*
FROM personal_message pm1
INNER JOIN
(
    SELECT LEAST(sender_id, receiver_id) AS sender_id,
           GREATEST(sender_id, receiver_id) AS receiver_id,
           MAX(created_date) AS max_created_date
    FROM personal_message
    GROUP BY LEAST(sender_id, receiver_id),
             GREATEST(sender_id, receiver_id)
) pm2
    ON LEAST(pm1.sender_id, pm1.receiver_id) = pm2.sender_id AND
       GREATEST(pm1.sender_id, pm1.receiver_id) = pm2.receiver_id AND
       pm1.created_date = pm2.max_created_date;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • +1 that does seem to work :) can you please elaborate how that works I have to map that query to sqlalchemy as I am using it as my ORM. – Ahtisham Nov 23 '20 at 04:20
  • that looks complex is there any other simpler way ? – Ahtisham Nov 23 '20 at 04:26
  • @Ahtisham It's not complex actually. I added a version using joins, but you can see it is actually a bit uglier than the row number query. – Tim Biegeleisen Nov 23 '20 at 04:34
  • I get this error on running last query `Unknown column pm2.recevier_id in on clause` – Ahtisham Dec 03 '20 at 12:06
  • ...then you're not running the second query in my answer. – Tim Biegeleisen Dec 03 '20 at 12:22
  • no I get the error while running the second query. Actually I am using my project on mysql 8.0 as well as mysql 5.7 – Ahtisham Dec 03 '20 at 12:47
  • @Ahtisham Many apologies...there was a typo in `receiver_id` in the `ON` clause, this was likely the source of the error. Please reload your page, copy the code, and try again. – Tim Biegeleisen Dec 03 '20 at 13:13
  • Okay that works but it gives last message of each user not like the first query which gives last message between two users. – Ahtisham Dec 03 '20 at 16:30
  • I tried this query `select * from personal_message where sender_id = 15 or receiver_id = 15 order by created_date desc limit 1` but that doesn't give right result I just want to get the same result your first query gives but with your second query please help. – Ahtisham Dec 04 '20 at 05:56
  • @Ahtisham I don't have time right now to debug this. I would have to setup a demo and then test the second query, and this could take at least 15-20 minutes. If I have time later today, I will try to have a look. – Tim Biegeleisen Dec 04 '20 at 05:58