How to use this laravel query builder for this SQL Query

April 9, 2022

I have this query:

SELECT `topic_posts`.*, `users`.`id` AS `adminID`, `users`.`name` as `adminName` FROM `topic_posts` INNER JOIN `topics` ON `topics`.`id` = `topic_posts`.`topic` INNER JOIN `users` ON `users`.`id` = `topic_posts`.`player` WHERE `users`.`playerAdminLevel` > 0 AND `topics`.`type` = 0 GROUP BY (`topic_posts`.`id`)

And I want to use in Laravel, something like:

$result = DB::table('topic_posts`)->join..etc
And also, to use ->paginate(20)

How can I do that?

>Solution :

There are few possibilities to achieve what you need.

Pure select, with your whole SQL query:

DB::select('select topic_posts,* .....');

Check for more here:
https://laravel.com/docs/4.2/database#running-queries

Or you can also go in the way you started building query, like:

DB::table('topic_posts')
        ->join('topics', 'topics.id', '=', 'topic_posts.topic')
        ->join('users', 'users.id', '=', 'topic_posts.player')
        ->select('`topic_posts`.*', 'users.id') ...

More can be found here:
https://laravel.com/docs/9.x/queries#inner-join-clause