Is there a way to order by according to "has count" to an eager loaded eloquent query?

Question

I have a query like this:

$users = User::with("purchases")->has("purchases",">",10)->orderBy("id","desc")->get();

I would like to order results by purchase count. For example the top buyer should be on the top or vice versa. Is there a way to do that ?

Answer 1

Looking at this you can try:

$users = User::with("purchases")
    ->has("purchases",">",10)
    ->orderBy("id","desc")
    ->get()
    ->sortBy(function($user){
        return $user->purchases->count();
    });

Doesnt sort it in sql but should do the job.

Answer 2

If you want to have your database (rather than PHP) sort the results, you have to use joins. Something like this:

$users = User::with('purchases')
    ->selectRaw('users.*, count(purchases.user_id) as aggregate')
    ->leftJoin('purchases', 'purchases.user_id', '=', 'users.id')
    ->groupBy('purchases.user_id')
    ->having('aggregate', '>', 10)
    ->orderBy('aggregate', 'desc')
    ->get();

It looks a bit messy so you can extract out into a query scope and do something like this:

$users = User::with('purchases')->orderByHas('purchases', '>', 10, 'DESC')->get();

And add this to your base model (or just User model):

public function scopeOrderByHas($query, $relation, $operator = '>=', $count = 1, $dir = 'DESC') {
    $table = $this->getTable();
    $relation = $this->$relation();
    $relatedTable = $relation->getRelated()->getTable();
    $primaryKey = $this->primaryKey;
    $foreignKey = $relation->getForeignKey();

    return $query->selectRaw("{$table}.*, count({$foreignKey}) as aggregate")
        ->leftJoin($relatedTable, $foreignKey, '=', "{$table}.{$primaryKey}")
        ->groupBy($foreignKey)
        ->having('aggregate', $operator, $count)
        ->orderBy('aggregate', $dir);
}