sql - 获取最后一条消息 postgres

Question

我正在使用 Laravel 构建类似聊天的东西，我想从用户 X 和 Y 那里得到最后一条消息：

我有这张表叫做“消息”

id integer
receiver_id integer references user
sender_id integer references user
message text
created_at datetime

在这里，我有一条消息，发送消息的时间和对象；

       $user = JWTAuth::toUser();
       $messages = DB::table("messages")
            ->select(DB::raw(
                "least(sender_id, receiver_id) as user_1,
               greatest(sender_id, receiver_id) as user_2,
               max(created_at) as last_timestamp,
               max(message) as message"))
            ->where("sender_id", $user->id)
            ->orWhere("receiver_id", $user->id)
            ->groupBy(DB::raw("least(sender_id, receiver_id), greatest(sender_id, receiver_id)"))
            ->orderBy("last_timestamp", "desc")
            ->get();

    $message_info = array();

    foreach($messages as $k => $m) {
        $message_info[$k]['message'] = $m->message;
        $message_info[$k]['last_message'] = $m->last_timestamp;
        if($m->user_2 == $user->id){
            $message_info[$k]['user_id'] = $m->user_1;
        }else{
            $message_info[$k]['user_id'] = $m->user_2;
        }
    }

这里我列出了有消息的用户

    $listUserHaveMessage = [];

    foreach ($messages as $message){
        if($message->user_2 == $user->id){
            $listUserHaveMessage[] = $message->user_1;
        }else{
            $listUserHaveMessage[] = $message->user_2;
        }
    }

而这里没有发送消息的用户

    $notMessage = User::where("client_id",'=',$user->client_id)
        ->where("id","!=", $user->id)
        ->where("is_visible",'=', true)
        ->whereNotIn("id", $listUserHaveMessage)
        ->get();
    foreach ($notMessage as $m){
        $listUserNotHaveMessage[] = $m->id;
    }

现在我合并数组和查询以获取所有用户

    $list_users = array_merge($listUserHaveMessage, $listUserNotHaveMessage);
    $idsImploded = implode(',',$list_users);
    $users = User::where("client_id",'=',$user->client_id)
        ->where("id","!=", $user->id)
        ->where("is_visible",'=', true)
        ->whereIn("id", $list_users)
        ->orderByRaw(DB::raw("position(id::text in '$idsImploded')"))
        ->get();

    $outPut = array();

    foreach($users as $k => $u){
        $outPut['usuarios'][$k] = $u;
    }

我的问题是：如何将最后一条消息合并到用户数组？有办法吗？？

谢谢

标签： sqllaravelpostgresql