CODEWITHSUNDEEP
mysqldatabasemessaging
Clement Sam
Clement Sam

Reputation: 750

Get last message between current user and others from sql

I want to retrieve the last messages between current user and others and display it in the user's messages page like how facebook does. I used this

select *
from ch_messages
where receiver='$current_user_id' or sender='$current_user_id' && (least(sender, receiver), greatest(sender, receiver), f_msg_date)     
in 
(
    select 
       least(sender, receiver) as x, greatest(sender, receiver) as y, 
       max(f_msg_date) as date
    from ch_messages
    group by sender, receiver
)

but it get all the messenges from a user to the current user and the current user's last message to the user. I want it like this image

This is my table structure

mid  => messages id
sender  => The sender of the message
receiver => The receiver of the message
msg   => The message sent
f_msg_date => date in which the message was sent

Upvotes: 0

Views: 445

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133370

You simply need to remove the quote around reciver in subquery and assuming the each message have an unique id column (auto increment) if you want the last message

  select *
  from ch_messages
  where receiver='$current_user_id' 
  or sender='$current_user_id' 
  AND ( id, least(sender, receiver), greatest(sender, receiver), f_msg_date )     
  in 
  (  select 
           max(id) 
         , least(sender, receiver) 
         , greatest(sender, receiver), 
           max(f_msg_date) 
      from ch_messages
      group by sender, receiver
  )

Upvotes: 1

Related Questions