I do have 2 tables linked with each other. Items that has an ID and quantity column, as well as Attendees that have id, item_id and some other data.
I would like to be able to see all the items that have:
a) no attendees at all.
b) less attendees than the quantity.
c) as many attendees as the quantity.
While a is easy to do with raw:
SELECT it.*, COUNT(att.item_id) AS atts, it.quantity as quant FROM items AS it LEFT JOIN attendees AS att on it.id = att.item_id GROUP BY it.id
and b and c can be done this way:
select * from (SELECT it.*, COUNT(att.item_id) AS atts, it.quantity as quant FROM items AS it LEFT JOIN attendees AS att on it.id = att.item_id GROUP BY it.id) as `i.*aq` where atts < quant; // or atts = quant
I cannot manage to find a way to do this thing with Eloquent. I wanted to do it with a scope like this:
Item.php public function scopeFull(Builder $query) { return $query->where('quantity', '=', $this->attendees()->count()); }
but it throws me an error about ‘attendees’.” not being a column name (the relation in other cases works fine though).
I really care about the code being clean, and I really do want to make it with either Eloquent models, or (less preferably) DB::selects.
Oh, and for those that just say to get all items as a collection to PHP already and filter it out – I really believe there is a clean way of achieving this via Fluent/Eloquent.
Thank y’all in advance!
EDIT:
Along with one of the answers I tried to use has() function, and while for case a it works fine, cases b and c would require an external parameter to come and see, while this solution should compare it live.
It should be something along these lines:
public function scopeFull(Builder $query) { return $query->has('attendees', '=', 'items.quantity'); // or $this->quantity }
Advertisement
Answer
Assuming you have a properly configured relation called attendees
on the items
model, what you want are the has
and doesntHave
methods
You can call these on the model itself easy enough, but if you really wanted to make scopes for them, they’d look something like this:
// a) no attendees at all. public function scopeWithoutAttendees(Builder $query) { return $query->doesntHave('attendees'); } // b) less attendees than the quantity. public function scopeFewerAttendeesThan(Builder $query, $quantity) { return $query->has('attendees', '<', $quantity); } // c) as many attendees as the quantity. public function scopeAttendeesCount(Builder $query, $quantity) { return $query->has('attendees', '=', $quantity); }
Note that whereHas
and whereDoesntHave
are also worth reading about there as they’ll let you use closures that can add more conditions on the relations if needed.