CODEWITHSUNDEEP
mysqlsqlgreatest-n-per-groupwindow-functionsmysql-8.0
techedifice
techedifice

Reputation: 163

Get unique list of conversation like what we see in any messaging app

My messaging table is:

CREATE TABLE messaging (
  msg_id SERIAL,
  sender_id BIGINT  NOT NULL ,
  receiver_id BIGINT NOT NULL ,
  msg text default NULL,
  media_link TEXT DEFAULT NULL,
  sent_time TIMESTAMP NOT NULL DEFAULT NOW(),
  received_time TIMESTAMP NULL,
  msg_type ENUM('text','link','file') default 'text',
  is_seen BINARY DEFAULT 0

  ) ENGINE=InnoDB;

Data of the table are:

+------+---------+-----------+------------------+----------+-------------------+-------------+--------+-------+
|msg_id|sender_id|receiver_id|msg               |media_link|sent_time          |received_time|msg_type|is_seen|
+------+---------+-----------+------------------+----------+-------------------+-------------+--------+-------+
|3     |1        |10         |hi                |          |2022-11-08 19:11:53|NULL         |text    |0      |
|4     |1        |10         |r u there?        |          |2022-11-08 19:12:46|NULL         |text    |0      |
|5     |7        |10         |hi                |NULL      |2022-11-09 14:11:41|NULL         |text    |0      |
|6     |7        |10         |where r u from?   |NULL      |2022-11-09 14:11:41|NULL         |text    |0      |
|7     |10       |1          |new here          |NULL      |2022-11-09 14:13:46|NULL         |text    |0      |
|8     |1        |7          |nice messaging app|NULL      |2022-11-09 14:14:17|NULL         |text    |0      |
|9     |7        |1          |kind of           |NULL      |2022-11-09 14:14:37|NULL         |text    |0      |
+------+---------+-----------+------------------+----------+-------------------+-------------+--------+-------+

I tried this statement:

with main as (
select
*,
row_number() over(partition by sender_id, receiver_id order by sent_time desc) as rank_
from messaging
)
select * from main where rank_ = 1

However, it gives just every combination of receiver_id and sender_id latest message:

+------+---------+-----------+------------------+----------+-------------------+-------------+--------+-------+-----+
|msg_id|sender_id|receiver_id|msg               |media_link|sent_time          |received_time|msg_type|is_seen|rank_|
+------+---------+-----------+------------------+----------+-------------------+-------------+--------+-------+-----+
|8     |1        |7          |nise messeging app|NULL      |2022-11-09 14:14:17|NULL         |text    |0      |1    |
|4     |1        |10         |r u there?        |          |2022-11-08 19:12:46|NULL         |text    |0      |1    |
|9     |7        |1          |kind of           |NULL      |2022-11-09 14:14:37|NULL         |text    |0      |1    |
|5     |7        |10         |hi                |NULL      |2022-11-09 14:11:41|NULL         |text    |0      |1    |
|7     |10       |1          |new here          |NULL      |2022-11-09 14:13:46|NULL         |text    |0      |1    |
+------+---------+-----------+------------------+----------+-------------------+-------------+--------+-------+-----+

What I need is to get sender_id and receiver_id unique constant what you use in creating unique columns, that means what we see in any messaging app: last message between unique users, like:

+------+---------+-----------+-------+----------+-------------------+-------------+--------+-------+-----+
|msg_id|sender_id|receiver_id|msg    |media_link|sent_time          |received_time|msg_type|is_seen|rank_|
+------+---------+-----------+-------+----------+-------------------+-------------+--------+-------+-----+
|9     |7        |1          |kind of|NULL      |2022-11-09 14:14:37|NULL         |text    |0      |1    |
+------+---------+-----------+-------+----------+-------------------+-------------+--------+-------+-----+
|7     |10       |1          |new here|NULL      |2022-11-09 14:13:46|NULL         |text    |0      |1    |
+------+---------+-----------+--------+----------+-------------------+-------------+--------+-------+-----+

Upvotes: 2

Views: 48

Answers (1)

GMB
GMB

Reputation: 222482

Your query gives you the latest message per sender/receiver tuple. I understand that you want the latest message per pair of users, regardless of which of the two sends or receive.

If so, you can just tweak the partition of row_number() in your existing query:

with main as (
    select m.*,
        row_number() over(
            partition by least(sender_id, receiver_id), greatest(sender_id, receiver_id) 
            order by sent_time desc
    ) as rn
    from messaging
)
select * from main where rn = 1

The lest/greaterst expression generates a "stable" set of user tuples, where the user with the lowest id consistently comes first in the partition.

Upvotes: 1

Related Questions