Laravel按最后一条消息对对话进行排序

问题描述

我有一个 Conversation 模型，其中有许多 ConversationMessage 模型.

I have a Conversation model that has many ConversationMessage models.

现在，我想根据对话的最后一条消息对对话进行排序.基本上就像WhatsApp.如何建立查询?

Now I want to sort the conversations based on the last message of the conversation. Basically like WhatsApp. How do I build the query?

作为我的代码的注释:对话包含一个用户( user_id )和关联的公司( company_id ).公司与用户具有 belongsToMany 关系.

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();

对 ConversationMessage 关系模型进行排序，但不对对话进行排序.如何根据最后一条消息对对话进行排序?

That sort the ConversationMessage relation models but not the conversations. How can I sort the Conversations based on the last message?

推荐答案

您可以在 Conversations 模型中添加 timestamp 字段，例如 last_message_at 并且当向该会话添加新消息时，会更新该字段，然后您可以根据 last_message_at 对结果进行排序.

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();

这篇关于Laravel按最后一条消息对对话进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！