I have a many to many relationship between users and locations. I want to filter my "attach user" select list to only show users whose company_id
matches the company_id
of the location I am currently on. I have created a static function called relatableUsers
which adds a "where" clause to the query.
public static function relatableUsers(NovaRequest $request, $query)
{
return $query->where('company_id', ???);
}
How do I pull the current location's company_id
into this where query? If I hard code a company_id
like below the filter works, so I know this is possible.
public static function relatableUsers(NovaRequest $request, $query)
{
return $query->where('company_id', 'FA624E60-DD37-11E8-A540-C3C0A709EE15');
}
The record information is not stored in the $request
or the $query
.
EDIT - adding relationships
User model:
public function company()
{
return $this->belongsTo(Company::class);
}
public function locations()
{
return $this->belongstoMany(Location::class);
}
Location model:
public function company()
{
return $this->belongsTo(Company::class);
}
public function users()
{
return $this->belongstoMany(User::class);
}
Company model relationships:
public function users()
{
return $this->hasMany(User::class);
}
public function location()
{
return $this->hasMany(Location::class);
}
You can retrieve the target Location
from the request like below.
public static function relatableUsers(NovaRequest $request, $query)
{
$location = $request->findResourceOrFail(); // Retrieve the location instance
return $query->where('company_id', $location->company_id);
}
So your locations
and users
tables both have a company_id
field, and you want to pull a list of users that have the same company_id
as the location you're working on. This can be done by filtering the existing relationship method:
<?php
class Location extends Model
{
public function relatableUsers()
{
return $this->users()->where("company_id", $this->company_id)->get();
}
}
Note that I call $this->users()
and not $this->users
. This return a query builder instance you can alter before talking to the database, rather than pulling all the users and sorting through the collection on the PHP side. Now you can call this on an instance of the Location
model:
<?php
$loc = Location::find($id);
$users = $loc->relatableUsers();