Laravel sortBy paginate

Question

I have a posts table and comments table, comment belongs to post, and I have the relationship setup in Post and Comment model. I did sort posts by the number of comments of each post like this:

      $posts = Post::with('comments')->get()->sortBy(function($post) {
           return $post->comments->count();
      });

What I wonder is how I can paginate these sorted posts?

      $posts = Post::with('comments')->get()->sortBy(function($post) {
           return $post->comments->count();
      })->paginate(20);

doesn't work and gives me error that says paginate is an undefined method.

score 6 · Accepted Answer · answered Oct 15 '14 at 13:37

I don't know if you can do it using Eloquent but you can use join for this:

$posts = Post::leftJoin('comments','posts.id','=','comments.post_id')->
               selectRaw('posts.*, count(comments.post_id) AS `count`')->
               groupBy('posts.id')->
               orderBy('count','DESC')->
               paginate(20);

However it seems that in this case all records are taken from database and displayed only those from paginator, so if you have many records it's waste of resources. It seems you should do manual pagination for this:

$posts = Post::leftJoin('comments','posts.id','=','comments.post_id')->
               selectRaw('posts.*, count(comments.post_id) AS `count`')->
               groupBy('posts.id')->
               orderBy('count','DESC')->
               skip(0)->take(20)->get();

using skip and take but I'm not Eloquent expert and maybe there's a better solution to achieve your goal so you can wait and maybe someone will give a better answer.

Thansk, so just to be clear, this solution won't fetch all records from database and then sort it out, it fetches only 20 records at the very begining right? — dulan, Oct 16 '14 at 01:13
@dulan, yes if we are talking about the 2nd code. In the 1st code all the records will be get from database and displayed only some of them so you need to choose the 2nd solution — Marcin Nabiałek, Oct 16 '14 at 05:48

score 2 · Answer 2 · answered Oct 15 '14 at 20:10

This sounds obvious, but Eloquent will not return a result set here, but rather it will return a collection.

If you dig into the source (Builder::get calls Builder::getFresh, which calls Builder::runSelect, which calls Connection::select), you'll find that it's intention is to simply return the results, which are then placed into a collection (which has the sortBy method).

/**
 * Run a select statement against the database.
 *
 * @param  string  $query
 * @param  array  $bindings
 * @param  bool  $useReadPdo
 * @return array
 */
public function select($query, $bindings = array(), $useReadPdo = true)
{
  return $this->run($query, $bindings, function($me, $query, $bindings) use ($useReadPdo)
  {    
    if ($me->pretending()) return array();

    // For select statements, we'll simply execute the query and return an array
    // of the database result set. Each element in the array will be a single
    // row from the database table, and will either be an array or objects.
    $statement = $this->getPdoForSelect($useReadPdo)->prepare($query);

    $statement->execute($me->prepareBindings($bindings));

    //** this is a very basic form of fetching, it is limited to the PDO consts.
    return $statement->fetchAll($me->getFetchMode());
  });  
}

If you want to have pagination without loading every item, then you need to use @Marcin's solution (duplicated below):

$posts = Post::leftJoin('comments','posts.id','=','comments.post_id')->
           selectRaw('posts.*, count(comments.post_id) AS `count`')->
           groupBy('posts.id')->
           orderBy('count','DESC')->
           skip(0)->take(20)->get();

score -1 · Answer 3 · answered Oct 15 '14 at 17:46

-1

Just remove the get() in the chained calls and see what you get, paginate should replace get() call.

answered Oct 15 '14 at 17:46

cdarken

419
4
9

1

It won't work. `sortBy` is used for result set so using paginate makes that sorting will be done only for first 20 rows and not for all records. That's the difference. First sort then paginate not opposite – Marcin Nabiałek Oct 15 '14 at 18:35
I recommended him to remove the get(), not to replace it with paginate(), paginate will replace it functionally speaking. – cdarken Oct 16 '14 at 06:37

Laravel sortBy paginate

3 Answers3

Linked