CODEWITHSUNDEEP
phpmysqllaravel
tnash
tnash

Reputation: 385

last message in conversation mysql Laravel eloquent

I'm trying to get the last message in MySQL using Laravel eloquent. I have tables

I tried the below code but it's not returning the last message.

    return $this->chat->join("chats_users", "chats_users.chat_id", "=", "chats.id")
                      ->join("chats_reply", "chats_reply.chat_id", "=", "chats.id")
                      ->where("chats_users.user_id", "=", $user_id)
                      ->take($limit)
                      ->skip($offset)
                      ->groupBy("chats_reply.chat_id")
                      ->get();

I also tried:

return \DB::select( \DB::raw("SELECT * FROM chats_reply msg inner join ( select max(created_at) created_at, message from chats_reply group by chat_id) m2 on msg.created_at = m2.created_at INNER JOIN chats_users mst ON mst.chat_id=msg.chat_id WHERE mst.user_id=$user_id") );

Upvotes: 0

Views: 876

Answers (1)

tnash
tnash

Reputation: 385

This actually worked. 

\DB::select( \DB::raw(
            "SELECT * FROM chats_reply msg

             INNER JOIN ( 

                  SELECT max(created_at) created_at FROM chats_reply GROUP BY chat_id ORDER BY created_at

            ) m2 

            ON msg.created_at = m2.created_at
            "
     ) 
  );

Upvotes: 1

Related Questions