I have a Conversation
model that has many ConversationMessage
models.
Now I want to sort the conversations based on the last message of the conversation. Basically like WhatsApp. How do I build the query?
As a sidenote to my code: Conversation contains a user (user_id
)
and a company associated (company_id
). Company has a belongsToMany
relation to user.
Conversation::whereHas('company', function ($q) use ($userId) { $q->whereHas('users', function ($q1) use ($userId) { $q1->where('users.id', $userId); }); })->orWhereHas('user', function($q) use ($userId) { $q->where('user.id', $userId); })->with(array('conversationMessage' => function($q) { $q->orderBy('created_at', 'DESC'); })->get();
That sort the ConversationMessage
relation models but not the conversations. How can I sort the Conversations
based on the last message?
Advertisement
Answer
You can add a timestamp
field like last_message_at
to your Conversations
model and when a new message is added to that Conversation
update that field, then later you can sort the result based on last_message_at
.
Or you can filter results in the query like below(Test it see if it works):
Conversation::whereHas('company', function ($q) use ($userId) { $q->whereHas('users', function ($q1) use ($userId) { $q1->where('users.id', $userId); }); })->orWhereHas('user', function($q) use ($userId) { $q->where('user.id', $userId); })->with(array('conversationMessage' => function($q) { $q->orderBy('created_at', 'DESC'); }) ->selectRaw("conversations.*, (SELECT MAX(created_at) from conversation_messages WHERE conversation_messages.conversation_id=conversations.id) as latest_message_on") ->orderBy("latest_message_on", "DESC") ->get();