Laravel Select Query and Pagination

I’ve been trying to perform a multiple table based query on my laravel project and I got it done for a while. Now, I realized I had to add a paginator for a better user experience.

The thing is that I want to convert my SELECT Query so I can use the paginate method. The query looks like this:

SELECT m.created_at as msg_created_at, m.id, m.on_thread_id, u.name, u.user_title, u.user_pic, u.created_at, u.msg_count, m.content, t.thread FROM messages m, users u, threads t WHERE t.id = '.$id.' AND m.thread_id = t.id AND m.creator = u.id ORDER BY m.on_thread_id

$id is a dynamic value variable based on the user reference.

I need your help Laravel Masters, thank You.

Answer

You can try like that:

$res = DB::table('messages')
    ->join('users', 'users.id', '=', 'messages.creator')
    ->join('threads', 'threads.id', '=', 'messages.thread_id')
    ->where('threads.id', $id)
    ->orderBy('messages.on_thread_id')
    ->get();