I need to check if the given user_id, location_id, and sub_location_id is belongs to the user on user_locations table. Based on this I need to give login permission to the user for the specific site.

Currently, I have the written code like below. But, don't know how to check the condition.

User::select('id')->with('user_locations:location')->where('user_id',$request->user_id)->get();

I need to put these conditions

1. is user found and active?
2. if user active. Then find the given sub location is tagged for the user.
3. if sub location found, then is the sub location active?

Below are the table structure.

Users Table

id (PK, AI),
user_id (UQ),
password,
status, ("Y"=> Active, "N"=> Inactive)
//other fields

public function user_locations(): HasMany
{
  return $this->hasMany(user_locations::class);
}

User_Locations Table

id (PK, AI),
user_id (FK),
location_id (FK),
sub_location_id (FK),
status ("Y"=> Active, "N"=> Inactive)

public function user(): BelongsTo
{
  return $this->belongsTo(User::class);
}

CodePudding user response：

I think you need to check relation existing

You can try this way for find user model

User::select('id')
        ->with('user_locations:location')
        ->where('user_id',$request->user_id)
        ->where('status','Y')
        ->whereHas('user_locations', function ($q) use ($request) {
            $q->where('user_locations.user_id', $request->user_id);
            $q->where('user_locations.status', 'Y');
        })
        ->get();

If you want just to check user existing you can try this

       User::where('user_id',$request->user_id)
        ->where('status','Y')
        ->whereHas('user_locations', function ($q) use ($request) {
            $q->where('user_locations.user_id', $request->user_id);
            $q->where('user_locations.status', 'Y');
        })
        ->exists();

You can read mode about this here