I have a problem with showing users based on locations,
My problem is that it displays user only, not the user's associated with the locations
I want the user to show him only the users in the locations it is linked in table (location_user)
I know the problem is from location_user', 'location_user.user_id', '=', 'users.id'
But I want the link is smart and I don't want to add a column in the user list with the name location_id
Because I want multiple locations
This is data of UsersController.php
$data = User::leftjoin('location_user', 'location_user.user_id', '=', 'users.id')
->where('location_user.user_id', auth()->user()->id)
->leftjoin('locations', 'locations.id', '=', 'location_user.location_id')
->where('locations.status', '1')
->select('users.*',)
->orderBy('status', 'DESC')->get();
Tables
users: https://i.stack.imgur.com/h3dID.jpg
location_user: https://i.stack.imgur.com/6KGZN.jpg
locations: https://i.stack.imgur.com/UDbDJ.jpg
Can anyone help me?
Thanks.
Firstly you don't need to associate table "location_user" because your user is associated with only one location, so you have here a MenyToOne relationship you can make it by adding these lines to your models :
class User extends Model
{
//here
public function location()
{
return $this->belongsTo(Location::class);
}
}
class Location extends Model
{
//here
public function users()
{
return $this->hasMany(User::class);
}
}
After that, you can query the members in the locations that are linked to a user by id like this :
$user = User::find(1); //get user by id : 1
$location = $user->location // get location associted with user id 1
$members = $location->users // get all members associated with the location of user id 1
If the case of the relationship ManyToMany, you still can use the same method just by modifying :
/*
public function location()
{
return $this->belongsTo(Location::class);
}
*/
by :
public function locations()
{
return $this->belongsToMany(Location::class,'location_user');
}
and :
/*
public function users()
{
return $this->hasMany(User::class);
}
*/
by :
public function users()
{
return $this->belongsToMany(User::class,'location_user');
}
After you can merge the members by collection methods : Collections Or by using whereHas and orHas
Based on your table structure - there is a many-to-many relation between User and Location via location_user pivot table.
If the respective relations are defined on both models like
//User Model
public function locations()
{
return $this->belongsToMany(Location::class);
}
//Location Model
public function users()
{
return $this->belongsToMany(User::class);
}
Then you can write a query to get all users that are associated with the logged in user's locations
$locationIds = auth()->user()->locations->pluck('id');
$users = User::query()
->whereHas('locations', fn($query) => $query->whereIn('id', $locationIds)
/** If you want results to include the currently logged in user
* remove the below where condition **/
->where('id', '<>', auth()->id())
->get();
OR using conventional anonymous closure functions instead of arrow functions syntax
$locationIds = auth()->user()->locations->pluck('id');
$users = User::query()
->whereHas('locations', function($query) use($locationIds){
$query->whereIn('id', $locationIds);
})
/** If you want results to include the currently logged in user
* remove the below where condition **/
->where('id', '<>', auth()->id())
->get();