Symfony - get latest conversation messages

Question

I have a user entity with two user foreign key (from_id and to_id) in a message entity like that :

So, all records are :

id     | from_id | to_id  |     datemessage        |  message        |
:------|:--------|:-------|:-----------------------|:----------------|
10     | 2       |  1     |  2017-09-17 18:45:46   |  redsfdffdfgd
13     | 2       |  1     |  2017-09-18 13:33:18   |  Hi You
15     | 2       |  1     |  2017-09-18 13:33:52   |  Hi You
17     | 2       |  1     |  2017-09-18 13:34:41   |  Hi You
5      | 4       |  2     |  2017-09-17 09:27:04   |  TEst
8      | 1       |  2     |  2017-09-18 15:01:03   |  testasdfas
9      | 1       |  3     |  2017-09-17 18:42:34   |  jjkjlkjkjkl
14     | 2       |  3     |  2017-09-18 13:33:32   |  Hi You
16     | 2       |  3     |  2017-09-18 13:34:30   |  Hey You
6      | 2       |  4     |  2017-09-17 10:14:31   |  test
7      | 2       |  4     |  2017-09-17 10:14:57   |  a bon voila
11     | 1       |  4     |  2017-09-18 13:32:20   |  Hey You
12     | 2       |  4     |  2017-09-18 13:32:57   |  Hey ZIoup
18     | 2       |  4     |  2017-09-18 13:34:51   |  Have a nice day

The result I want for the user 2 is :

id     | from_id | to_id  |     datemessage        |  message        |
:------|:--------|:-------|:-----------------------|:----------------|
8      | 1       |  2     |  2017-09-18 15:01:03   |  testasdfas
16     | 2       |  3     |  2017-09-18 13:34:30   |  Hey You
18     | 2       |  4     |  2017-09-18 13:34:51   |  Have a nice day

I want every last records for user "2" (distinct from_id and to_id) Thanks for everybody could help me :)

I tried this query, but it don't sort my query by date :

SELECT distinct_id,
       id,
       from_id,
       to_id,
       datemessage,
       message,
FROM
  (SELECT message.from_id AS from_id,
          message.id AS id,
          message.from_id AS distinct_id,
          message.to_id AS to_id,
          message.datemessage AS datemessage,
          message.message AS message,
          fos_user.id AS user_id,
          fos_user.username AS username
   FROM message
   LEFT JOIN fos_user ON message.from_id = fos_user.id
   WHERE message.to_id = :user_id
   UNION SELECT message.to_id AS to_id,
                message.id AS id,
                message.to_id AS distinct_id,
                message.from_id AS to_from,
                message.datemessage AS datemessage,
                message.message AS message,
                fos_user.id AS user_id,
                fos_user.username AS username
   FROM message
   LEFT JOIN fos_user ON message.to_id = fos_user.id
   WHERE message.from_id = :user_id ) AS message
GROUP BY distinct_id
ORDER BY DATE(datemessage) ASC

Thanks everybody for your help :)

If you need last records of user 2 from each conversation then why record no 8 , 1, 2 is required isn't this to be 2 ,1 ? — M Khalid Junaid, Sep 18 '17 at 18:05
Because to_id "2" is user "2" and from_id is user "2" too. I want all last exchange between one user and user "2". Regardless of the meaning of the exchange. Facebook have about the same model of exchange, no ? — cretthie, Sep 18 '17 at 18:10

M Khalid Junaid · Accepted Answer · 2017-09-18T18:17:10.947

To pick the latest message from conversation for from_id = 1 you can use below query

select m.*
from 
message m
left join message m1 on (m.from_id = m1.from_id 
                         and m.to_id = m1.to_id
                         and m.messagedate < m1.messagedate)
where m1.id is null
and m.from_id = 1;

To show the names in query you can do a left join to user table

select m.*,u.username,u1.username
from 
message m
left join message m1 on (m.from_id = m1.from_id 
                         and m.to_id = m1.to_id
                         and m.messagedate < m1.messagedate)
left join fos_user u on(m.from_id = u.id)
left join fos_user u1 on(m.to_id = u1.id)
where m1.id is null
and m.from_id = 1;

DEMO

EDIT To get last records for user "2" (distinct from_id and to_id)

select m.*
from 
message m
left join message m1 on (
  (
    (m.from_id = m1.from_id and m.to_id = m1.to_id)
                          or
    (m.from_id = m1.to_id and m.to_id = m1.from_id )
  )
   and m.datemessage < m1.datemessage
)
where m1.id is null
and (m.from_id = 2 or m.to_id = 2)
order by m.id

Updated Demo

Hi, Thank you very much. I don't understand why m1.id have to be null, but it works fine :) — cretthie, Sep 18 '17 at 14:25
Great !!!! Thank you very much ! :) That is exactly what I am looking for. Thanks ! — cretthie, Sep 18 '17 at 18:25

Symfony - get latest conversation messages

1 Answers1