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.

Answer 1

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.

Answer 2

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();