Laravel Eloquent

Using Eloquents withCount() and orderBy() with relation#

Eloquent has function called withCount(): it helps to get the amount of related records inside of the main object. https://laravel.com/docs/eloquent-relationships#counting-related-models

Sometimes using withCount and orderBy may be slow and using Query Builder might be better approach. Example:

    User::query()
        ->withCount([
            'ratings' => function ($query) use ($from, $to) {
                return $query->whereBetween('date', [$from, $to]);
            }
        ])
        ->orderByDesc('ratings_count')
        ->limit(20);

Translates to raw SQL:

select
  "user".username,
  (
    select
      count(id)
    from
      "rating"
    where
      "user"."id" = "rating"."user_id"
      and "date" between '2020-09-01 00:00:00'
      and '2020-09-30 23:59:59'
  ) as ratings_count
from
  "user"
order by
  ratings_count desc
limit
  20;
-- takes ~370ms

Instead we can write this:

    User::query()
        ->join('rating', 'user.id', 'rating.user_id')
        ->select('user.*', DB::raw('count(rating.id) as ratings_count'))
        ->whereBetween('rating.date', [$from, $to])
        ->groupBy('user.id')
        ->orderBy('ratings_count', 'desc');

select
	"user" .username ,
	count(rating.id) as ratings_count
from
	"user"
inner join "rating" on
	"user"."id" = "rating"."user_id"
where
	"rating"."date" between '2020-09-01 00:00:00' and '2020-09-30 23:59:59'
group by
	"user"."id"
order by
	"ratings_count" desc
limit 20
-- takes ~50ms