Question 15914 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Eloquent Query Issue with checking data through relationship

I have a problem with retrieving the data from Users table:

User:
-id int
-name string
-compartment_id int

This is related with compartments table:

Compartment:
-id int
-name string
-country_id int

Which is related to countries table:

Country:
-id int
-name string

User model:

public function compartment()
{
    return $this->belongsTo(Compartment::class, 'compartment_id');
}

Compartment model:

public function country()
{
    return $this->belongsTo(Country::class, 'country_id');
}

public function users()
{
    return $this->hasMany(User::class);
}

Country model:

public function compartments()
{
    return $this->hasMany(Compartment::class);
}

I am using Livewire PowerGrid to display data in data tables.

I wanted to get All Users with compartment_id = null and All Users that compartment->country_id is equal to Auth::user()->compartment->country_id.

I’m stuck with:

User::whereIn('compartment_id', Compartment::select('country_id')
->where('country_id', Auth::user()->compartment->country_id))->where('compartment_id', null);

It must be the builder, without ->get() because it’s interpreted by PowerGrid correctly.
Can someone instruct?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found the solution by myself.

    User::whereIn('compartment_id', Compartment::select('compartment_id')
     ->where('country_id', Auth::user()->compartment->country_id))
     ->where('id', '!=', Auth::user()->id)
     ->orWhereNull('compartment_id');

    I thought wrongly (by mistake) that it should be AND clause, not OR. Thanks all for help.


  2. 0

    You can use the below query,

    You will get all users without compartment assigned and those whose compartment country matches with compartment

    User::whereHas('compartment',function($q){
  $q->where('country_id', Auth::user()->compartment->country_id);
})
->orWhereNull('compartment_id');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search