0

socket.io messaging system where the users id in the messages table is in both the from_id and to_id column.

I want to get the last message between a user and each friend from the messages table whether its a sent or received message id.

messages table:

id    from_id     to_id        message
1       20          30           xxx 
2       30          20           xxx 
3       20          71           xxx 
4       20          40           xxx 
5       20          71           xxx 
6       40          20           xxx 
7       71          20           xxx 
8       18          20            xxx 
9       20          18           xxx 
10      18          20           xxx

my out put result must be like so:

Between 20 and 30  last id = 2
between 20 and 71  last id = 7
Between 20 and 40  last id = 6
Between 20 and 18  last id = 10

I have no code sample:

My goal is to get the last message between the users id and each friend and display the first few characters like it is in whatsapp when you view the chats page.

There is the friends image, friends name and a teaser of the last message between the two of you.

Antony Falencikowski
  • 51
  • 7
  • 1
    Use `MAX(id)` and `GROUP BY from_id, to_id` – Barmar Jun 12 '20 at 18:55
  • @Barnar Thanks for the reply. I did look at the similar question, its not the same as my question. Under my messages table the users id is in both the from_id and to_id column. I kind of get what you saying with the MAX(id) and group By i must just try to figure out my "WHERE" clause... Where user id in/not in ... this is where im stuck – Antony Falencikowski Jun 12 '20 at 19:14
  • `GROUP BY IF(from_id = 20, from_id, to_id), IF(to_id = 20, from_id, to_id)` – Barmar Jun 12 '20 at 19:22
  • `WHERE 20 IN (from_id, to_id)` – Barmar Jun 12 '20 at 19:22
  • You can also assign aliases in the `SELECT` list: `SELECT IF(from_id = 20, from_id, to_id) AS this_id, IF(to_id = 20, from_id, to_id) AS friend_id`. Then use `GROUP BY this_id, friend_id` – Barmar Jun 12 '20 at 19:24
  • @Barmar This looks promising i will check it out now... Thanks for the patience.. – Antony Falencikowski Jun 12 '20 at 21:02
  • i tried every possible version of these tips, but no luck.. The results i did get were close though..... I found this topic which solved my question: [link]https://stackoverflow.com/questions/48884044/mysql-get-ordered-list-of-contacts-by-last-message-sent-received – Antony Falencikowski Jun 13 '20 at 18:21

0 Answers0