Laravel alternative to paginate on collection?

1
votes

On my website, I have Submissions, and submissions can have comments.

Comments can have upvotes and downvotes, leading to a total "score" for the comment.

In this example, before passing the comments to the view, I sort them by score.

$comments = Comment::where('submission_id', $submission->id)->where('parent_id', NULL)->get();

$comments = $comments->sortByDesc(function($comment){
    return count($comment['upvotes']) - count($comment['downvotes']);
});

This works fine. The higher the score of a comment, the higher it is sorted.

However, I want to paginate these results.

If I do ->paginate(10) instead get(), the following sortByDesc will only sort those 10 results.

So logically I would want to add the paginator after the sortByDesc like so:

$comments = $comments->sortByDesc(function($comment){
    return count($comment['upvotes']) - count($comment['downvotes']);
})->paginate(10);

However this will return the error:

Method Illuminate\Database\Eloquent\Collection::paginate does not exist.

as expected.

My question is, what is the alternative to using paginate in this situation?

EDIT:

When trying the response of @party-ring (and switching the double quotes and single quotes) I get the following error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '["upvotes"]) - count($comment["downvotes"]) desc limit 10 offset 0' at line 1 (SQL: select * from comments where submission_id = 1 and parent_id is null order by count($comment["upvotes"]) - count($comment["downvotes"]) desc limit 10 offset 0)

3
laravel

3 Answers

0
votes

You are trying to paginate after the get, the solution i try on my website is this and it works

$users = User::where('votes', '>', 100)->get();

$page = Input::get('page', 1); // Get the ?page=1 from the url
$perPage = 15; // Number of items per page
$offset = ($page * $perPage) - $perPage;

return new LengthAwarePaginator(
    array_slice($users->toArray(), $offset, $perPage, true), // Only grab the items we need
    count($users), // Total items
    $perPage, // Items per page
    $page, // Current page
    ['path' => $request->url(), 'query' => $request->query()] // We need this so we can keep all old query parameters from the url
);
0
votes

You could add a macro:

if (!Collection::hasMacro('paginate')) {
    Collection::macro('paginate', function ($perPage = 25, $page = null, $options = []) {
        $options['path'] = $options['path'] ?? request()->path();
        $page = $page ?: (Paginator::resolveCurrentPage() ?: 1);
        return new LengthAwarePaginator(
            $this->forPage($page, $perPage)->values(),
            $this->count(),
            $perPage,
            $page,
            $options
        );
    });
}

Then you can use a collection to paginate your items:

collect([1,2,3,4,5,6,7,8,9,10])->paginate(5);

See Extending Collections under Introduction

0
votes

Give this a try:

$comments = Comment::where('submission_id', $submission->id)
    ->where('parent_id', NULL)
    ->orderBy(DB::raw("count($comment['upvotes']) - count($comment['downvotes'])"), 'desc')
    ->paginate(10);`

SortBy returns a Collection, whereas you can only call paginate on an instance of QueryBuilder. OrderBy should return an instance of QueryBuilder, and you should be able to do the subtraction using a DB::raw statement.

** edit

I have just read about orderByRaw, which might be useful in this scenario:

$comments = Comment::where('submission_id', $submission->id)
    ->where('parent_id', NULL)
    ->orderByRaw('(upvotes - downvotes) desc')
    ->paginate(10);`

You might have to play around a bit with your subtraction above as I don't know the structure of your comments table.

A couple of links which might be useful: