Laravel Pagination with a Raw Query

Question

I'm using a raw SQL query in Laravel, because it was easier to me at the beginning to make complex queries, so I used them like that.

Now I want to get a list of comments of an user 7, and I would like to paginate the comments, in 5.

That's what I have:

$comments = DB::select( DB::raw("SELECT users.name,  reviews.comment, reviews.comment_score FROM reviews JOIN users WHERE reviews.user_id = :logged_user;")
   ,array('logged_user' => $user_id,));

So this query depends on the user which is consulting the view, receives a variable.

All I see for pagination is with Eloquent queries, and I don't find any clear usage for this.

If I add '->paginate(5)' at the end of the last parenthesis, or later to the variable like $comments->paginate(5); I get a:

Error: Call to a member function paginate() on array

Some light on this??

Thanks a lot! =)

ismael ansari ismael ansari · Accepted Answer · 2017-05-05T16:14:33

You do something like this for pagination.

$perPage = $request->input("per_page", 10);
$page = $request->input("page", 1);
$skip = $page * $perPage;
 if($take < 1) { $take = 1; }
if($skip < 0) { $skip = 0; }

$comments = DB::select( DB::raw("SELECT users.name,  reviews.comment, reviews.comment_score FROM reviews JOIN users WHERE reviews.user_id = :logged_user;"),array('logged_user' => $user_id,));

$totalCount = $comments->count();
$results = $comments
    ->take($perPage)
    ->skip($skip)
    ->get();
$paginator = new \Illuminate\Pagination\LengthAwarePaginator($results, $totalCount, $take, $page);

return $paginator;

Hope it will help you!

Laravel Pagination with a Raw Query

1 Answers