phpmyadmin Question posted in
The official documentation can be found here.

Select last message between a sender and a receiver in SQL – Phpmyadmin

I’m currently trying to get the last messages in a database in order to make a similar system to other messages system (like Facebook Messenger or WhatsApp) but I want to get the last messages of a "conversation" by the sender ID (id_emmeteur_id) and the receiver ID (id_destinataire_id).

The problem is that I only have succeeded to get all messages and I would like to get, depending on the connected user, only the last message between two users (the one that is connected and another one, depending on which ID I have in my messages table).

I’ve put my "messages" table structure below with some examples, hoping that it could be helpful.

id|id_emmeteur_id|id_destinataire_id|contenu
--------------------------------------------
 1|             3|                 1|test
 2|             3|                 1|another test
 3|             1|                 3|test number 3
 4|             1|                 2|another user

With these examples, logically, I should get (if the connected user has the ID "1") the messages "test number 3" and "another user" because the last message between the user with ID "1" and ID "3" is "test number 3" and because "another user" is the only message between the user with ID "1" and ID "2" but I think that my query is incorrect. I’ll also put the query that I’ve found in case that it could help you.

select *
  from message
  where id_destinataire_id=3 
  or id_emmeteur_id=1 
  AND ( id, least(id_emmeteur_id, id_destinataire_id), greatest(id_emmeteur_id, id_destinataire_id))     
  in 
  (  select 
           max(id) 
         , least(id_emmeteur_id, id_destinataire_id) 
         , greatest(id_emmeteur_id, id_destinataire_id)
      from message
      group by id_emmeteur_id, id_destinataire_id
  )

Thanks in advance for your help. (I hope that my DB structure isn’t incorrect)

Tags:

2

Answers


  1. 0

    You can use not exists as follows:

    select t.*
  from your_table t
 where 1 in (t.id_emmeteur_id, t.id_destinataire_id)
  and not exists 
      (select * from your_table tt
        where t.id_emmeteur_id = tt.id_emmeteur_id  
          and t.id_destinataire_id = tt.id_destinataire_id)
          and tt.id > t.id)
    Login or Signup to reply.
  2. 0

    One way would be to Get the last conversation by to and from ids and filter results based on that.

    WITH LastMessages AS 
(
    SELECT 
        id,
        ROW_NUMBER()OVER(PARTITION BY id_destinataire_id ORDER BY ID) last_destinataire_id,
        ROW_NUMBER()OVER(PARTITION BY id_emmeteur_id ORDER BY ID) last_emmeteur_id,
        id_destinataire_id to_id,
        id_emmeteur_id from_id
        contenu
    FROM
        message
    WHERE
        id_emmeteur_id = 3 OR id_destinataire_id = 3
)
SELECT 
    id, to_id, from_id, contenu
FROM 
    LastMessages 
WHERE
    last_destinataire_id = 1 OR  last_emmeteur_id = 1
ORDER BY
    id;

    No CTE Version

    SELECT 
    id,
    to_id,
    from_id,
    contenu
FROM 
(
    SELECT 
        id,
        last_destinataire_id = ROW_NUMBER()OVER(PARTITION BY id_destinataire_id ORDER BY ID),
        last_emmeteur_id = ROW_NUMBER()OVER(PARTITION BY id_emmeteur_id ORDER BY ID),
        to_id = id_destinataire_id,
        from_id =  id_emmeteur_id,
        contenu
    FROM
        message
    WHERE
        id_emmeteur_id = 3 OR id_destinataire_id = 3
)AS X
WHERE
    last_destinataire_id = 1 OR  last_emmeteur_id = 1
ORDER BY
    id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search