Show users by "location_user" in laravel-8-CodePudding

I have a problem with showing members based on locations,

My problem is that it displays user only, not the user's associated with the locations

I want the member to show him only the members 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 member 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.

CodePudding user response：

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

CodePudding user response：

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)
    ->get();