Laravel: How to write a join count query on belongsToMany relationships?

0
votes

I got the following:

  • User, Role, with a role_user pivot table and a belongsToMany relationship
  • User, Location, with a location_user pivot table and a belongsToMany relationship

There's 2 roles for the user: owner & gardener

Location has a 'gardeners_max' field

In model Location:

protected $appends = ['is_full'];

public function getIsFullAttribute()
{
    return $this->attributes['name'] = $this->remainingGardeners() <= 0;
}

public function countGardeners() 
{
    return $this->gardeners()->count();
}

public function remainingGardeners() 
{
    return $this->gardeners_max - $this->countGardeners();
}

Now, doing that : 

Location::all();

I get that :

[
 {
   name: 'LocationA',
   gardeners_max: 3,
   owners: [...],
   garderners: [...]
   ...
   is_full: false
 }
]

which is cool. BUT... it's not possible to do a WHERE clause on the appended attribute.

Location::where('is_full',true)->get() // Unknown column 'is_full' in 'where clause'

So i'd like to write a join query so I can do a where clause on is_full

And I just can't find the way. Any help will be greatly appreciated!

IMPORTANT:

I know the filter() method to get the results but I need to do a single scopeQuery here

2
laraveleloquentquery-builder

2 Answers

0
votes

You could try to manipulate the Collection after loading the object from database:

Location::get()->where('is_full', true)->all();

(You have to use get first then all, not sure it works otherwise)

Not sure it's optimized thought.

0
votes

You can make scope in your location model like this

public function scopeFull(Builder $query)
{
    return $query->where('is_full', true);
}

Now you just get all location like this

Location::full()->get();