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?
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
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`)
would be something like that (i have to change a little the query because it seems like you are not using group by correctly):
DB::table('topic_posts')
->join('topics', 'topic.id', '=', 'topic_posts.topic')
->join('users', 'users.id', '=', 'topic_posts.player')
->select('topic_posts.id', 'users.id as adminID', DB::raw('count(topic_posts.id) as totalTopicPosts'))
->where('users.playerAdminLevel', '>', 0)
->where('topics.topics', 0)
->groupBy('topic_posts.id', 'users.id')
->paginate(20)
But if you want to use a group by, first make sure that you have some aggregations like a count or whatever. check the documentation here : https://www.tutorialspoint.com/sql/sql-group-by.htm
