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.