How to sort Laravel eloquent result by another table column

Question

I have a very simple chat application which have 3 tables users , chat_rooms and chats table for sotring every users messages.

I want to show chat rooms in a box in my view but i want them to be sorted by messages created_at so that the chat rooms that have newest messages be on the top.

the query that i use is

ChatRoom::with('user')
            ->join('chats','chat_rooms.room_id','chats.room_id')
            ->select('chat_rooms.*','chats.created_at as c','chats.message')
            ->orderBy('c','DECS')
            ->paginate(5);

this works fine but returns all chats i tried to use groupBy('room_id') on my query but then it stoped working

i could use collections methods to fix this but then i could not use pagination and i realy dont want to use collection methods in this case.

note that im using laravel 5.4

Answer 1

Your eloquent pagination run on ChatRoom table Not on messages. So you can try another ways to load your message under chatroom.

• If you have a lot of chatroom and want to paginate both chatroom and their messages, you can paginate chatroom (without join to messages) and run eloquent for each chatroom messages separately.
• you can also use lazy load for messages on view but you must control messages paginate yourself

-------------EDITED-------------

Show chatroom with new messages:

Laravel <6

ChatRoom::select(['chat_rooms.*', 'chats.created_at as c'])
            ->join(DB::raw("(SELECT chats.room_id, max(chats.created_at) as created_at
                FROM chats
                GROUP BY room_id
                ORDER BY created_at DESC
                ) as chats"), function ($join) {
                $join->on("chat_rooms.id", "=", "chats.room_id");
            })
            ->orderBy('c', 'DECS')
            ->paginate(5);

Laravel >=6

$chatQuery = Chat::selectRaw("chats.room_id, max(chats.created_at) as created_at")
            ->groupBy("chats.room_id")
            ->orderBy("created_at", "desc");

ChatRoom::select(['chat_rooms.*', 'chats.created_at as c'])
  ->joinSub($chatQuery, 'chats', function ($join) {
       $join->on('chat_rooms.id', '=', 'chats.room_id');
  })
  ->orderBy('c','DECS')
  ->paginate(5);