CODEWITHSUNDEEP
phplaraveleloquent
foob.ar
foob.ar

Reputation: 485

How to compare related count with own column in Laravel Eloquent?

Assume we have an agents table with a quota column and a many-to-many relationship to tickets. With Laravel Eloquent ORM, how can I select only agents having less or equal number of 'tickets' than their 'quota'?

Eager-loading objects must be avoided.

class Agent extends Model {

   public function tickets()
   {
      return $this->belongsToMany(Ticket::class, 'agent_tickets')
          ->using(AgentTicket::class);
   }

   public function scopeQuotaReached($query)
   {
      // Does not work. withCount is an aggregate.
      return $query->withCount('tickets')
          ->where('tickets_count', '<=', 'quota');

      // Does not work. Tries to compare against the string "quota".
      return $query->has('tickets', '<=', 'quota');
   }

}

Is there a more eloquent (pun intended) way to solve this than using a DB::raw() query with joining and grouping and counting manually?

Upvotes: 5

Views: 3577

Answers (2)

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25906

Derived columns like tickets_count can only be accessed in the HAVING clause.

Since there is no havingColumn() method, you'll have to use a raw expression:

$query->withCount('tickets')->having('tickets_count', '<=', DB::raw('quota'));

Upvotes: 4

Kenny Horna
Kenny Horna

Reputation: 14241

At a database level I don't know how to achieve this, but you could do it at a Collection level.

// Get users
$agents = Agent::withCount('tickets')->get();
// filter
$good_agents = $agents->filter(function ($agent, $key) {
                   return $agent->tickets_count >= $agent->quota;
               })
               ->all();

Of course you can inline it:

$good_agents = Agent
    ::withCount('tickets')
    ->get()
    ->filter(function ($agent, $key) {
        return $agent->tickets_count >= $agent->quota;
    })
    ->all();

Upvotes: 0

Related Questions