Skip to content
Advertisement

Proper Laravel way to get a relation basing on both tables

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.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement