CODEWITHSUNDEEP
mysqllaravel
Nimesh Patel
Nimesh Patel

Reputation: 804

how can i get last record for chat conversion between two users?

=> I want to get the last record for chat conversion between two users based on created date field for getting the last message. below is my table

enter image description here

I have tried this query for getting the result of the last message of chat conversion but I can't get them right result.

select `chat_messages`.`id`, `chat_messages`.`message`, `chat_messages`.`created_at` 
from `chat_messages`
where `chat_messages`.`is_group` = 0 
and `chat_messages`.`deleted_at` is null 
and (`sender_id` =141 or `client_id` = 141)
group by `chat_messages`.`message_token` 
order by `chat_messages`.`created_at` desc

=> Current Result :-

enter image description here

=> Should be:-

enter image description here

Note:- This functionality is same like a chat functionality and i don't want to get only one record.

Thanks.

Upvotes: 0

Views: 153

Answers (4)

Teoman Tıngır
Teoman Tıngır

Reputation: 2866

I assume that you have Message model (you're using laravel)

Below query should give you what do you want.

Message::whereSenderIdAndClientId($userId1,$userId2)
       ->orWhere([
             ["sender_id", $userId2],
             ["client_id",$userId1] 
         ])
       ->orderBy("created_at","desc")
       ->first()

Get messages between sender and client, order messages by created_at - desc then take first message which is last message between each other.

Upvotes: 0

Jose Luis Poveda
Jose Luis Poveda

Reputation: 36

Why are you grouping by message_token?

I think this query will be valid:

select `chat_messages`.`id`, `chat_messages`.`message`, `chat_messages`.`created_at` 
from `chat_messages`
where `chat_messages`.`message_token` = '144_141'
and `chat_messages`.`is_group` = 0 
and `chat_messages`.`deleted_at` is null 
order by `chat_messages`.`created_at` desc
limit 1

After your edit:


select `chat_messages`.`id`, `chat_messages`.`message`, `chat_messages`.`created_at`
from (select * from `chat_messages` order by `chat_messages`.`created_at` desc) as chat_messages
where `chat_messages`.`is_group` = 0
and `chat_messages`.`deleted_at` is null
and (`sender_id` =141 or `client_id` = 141)
group by `chat_messages`.`message_token`;

Upvotes: 1

Vidal
Vidal

Reputation: 2621

If you group by message_token you will join all those messages, you will have to select that message tread in an where statement where message_token = XXX.

SELECT 
    `chat_messages`.`id`, 
    `chat_messages`.`message`, 
    `chat_messages`.`created_at` 
FROM 
    `chat_messages`
WHERE 
    `chat_messages`.`is_group` = 0 
    and `chat_messages`.`deleted_at` is null 
    and (`sender_id` =141 or `client_id` = 141)
    and `chat_messages`.`message_token`= "146_141"
ORDER BY 
    `chat_messages`.`created_at` desc, 
    `chat_messages`.`id` desc

Upvotes: 1

RiggsFolly
RiggsFolly

Reputation: 94642

The group by was causing you the problem. All you need to do is sort desc and then limit the result set to one row

select `chat_messages`.`id`, `chat_messages`.`message`, `chat_messages`.`created_at` 
from `chat_messages`
where `chat_messages`.`is_group` = 0 
and `chat_messages`.`deleted_at` is null 
and (`sender_id` =141 or `client_id` = 141)
order by `chat_messages`.`created_at` desc
LIMIT 1

Upvotes: 1

Related Questions