filter many to many relation result by column in pivot table

$normalEmployees = Employee::with(['workingCompanies' => function($query) use ($role) {
    $query->where('employee_role',  $role); 
}])->get();

Is this working?

Everything is correct except you need to use whereHas instead of with:

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role) {
    $query->withPivot('employee_role')->wherePivot('employee_role',$role);
})->get();

Let me try out tomorrow and feedback. Thanks

For information, you filtered the relation in your first example.

So you took all employees, but for them, only a part of working companies.

And as you requested only one company, ypu should filter that too

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role, $company) {
    $query->withPivot('employee_role')->wherePivot('employee_role',$role)
->wherePivot('company_id', $company);
})->get();

Really not sure that 'withPivot' is mandatory if you already declare the relation, but I'll let you try.

for Sergiu17 answer, I got this :

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where `employee_role` = ?)) and active = 1 and outsider=0' at line 1 (SQL: select * from `employees` where (`workingCompanies` = (select * where `employee_role` = 6)) and active = 1 and outsider=0)

for devk answer, I got this:

Call to undefined method Illuminate\Database\Query\Builder::withPivot()

I think both are not the right solutions ... And of course Vilfago's answer is similar to devk.

The funny thing is, when I try to debug, the query Laravel generated using my original solution is :

select `companies`.*, `company_employee`.`employee_id` as `pivot_employee_id`, `company_employee`.`company_id` as `pivot_company_id`, `company_employee`.`employee_role` as `pivot_employee_role` from `companies` inner join `company_employee` on `companies`.`id` = `company_employee`.`company_id` where `company_employee`.`employee_role` in ('4', '6', '7', '8', '9', '10', '11') and `company_employee`.`employee_id` in ('13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '26', '27', '28', '29', '30', '31', '32', '33', '35', '37', '38', '39', '41', '42', '43', '44', '45', '46', '47', '49', '50', '51', '53', '54', '55', '56', '57', '58', '59', '60', '61', '62', '63', '64', '65', '66', '68', '69', '70', '71', '72', '73', '74', '75', '76', '77', '78', '79', '80', '81', '82', '83', '84', '85', '86') and `company_employee`.`employee_role` = '6'

I try running it in Sequel Pro, MySQL 5.6.35 , it DOES give the right results. Somehow in Laravel, it returns the whole table ... any idea why ??

Even more weird, out of nowhere, there's a

select * from `employees` where active = 1 and outsider =0

generated before the above query. I did some tracing and it really boils down to this call

$normalEmployees = Employee::with(['workingCompanies' => function ($query) use ($role) {
                $query->withPivot('employee_role')->wherePivot('employee_role',$role);
            }])->get();

that generated the select * query ... totally lost .. This is my workingCompanies relation

public function workingCompanies($role = null) {
        $normalEmployeeRoles = $role ? [$role] : EmployeeRole::employeeRoles();
        return $this->belongsToMany(Company::class)->withPivot('employee_role')->wherePivotIn('employee_role', $normalEmployeeRoles);
    } 

Anyone has any idea ??

For Eloquent this query

$normalEmployees = Employee::with(['workingCompanies' => function ($query) use ($role) {
                $query->withPivot('employee_role')->wherePivot('employee_role',$role);
            }])->get();

is the same as (consider it as "pseudocode")

$employees = Employee::all();
$companies = WorkingCompany::join('employee_role', '[.. join condition ..]')->whereIn('employee_role.employee_role', $normalEmployeeRoles);

$normalEmployees = consolidate($employees, $companies);

so, it's normal you have 2 queries.

Have you tried :

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role, $company) {
    $query->wherePivot('employee_role',$role)->wherePivot('company_id', $company);
})->get();

Or maybe, give us the relation you had in your models ?

I'm not putting $company in because I want to get all employee of $employee_role no matter which company he's working at. Here's what I got running whereHas

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'pivot' in 'where clause' (SQL: select * from `employees` where exists (select * from `companies` inner join `company_employee` on `companies`.`id` = `company_employee`.`company_id` where `employees`.`id` = `company_employee`.`employee_id` and `pivot` = employee_role and `pivot` = company_id and `company_employee`.`employee_role` in (4, 6, 7, 8, 9, 10, 11)) and active = 1 and outsider =0)

I gave you the workingCompanies relation above, what else do you need ?

Wait, you sure it's implemented this way ??

$employees = Employee::all();
$companies = WorkingCompany::join('employee_role', '[.. join condition ..]')->whereIn('employee_role.employee_role', $normalEmployeeRoles);

$normalEmployees = consolidate($employees, $companies);

Because if I'm right, what it does it basically returning the whole Employee table, and eagerload 'workingCompanies' where condition met ... no wonder I got the whole table back

Sorry, don't see that

public function workingCompanies($role = null) {
        $normalEmployeeRoles = $role ? [$role] : EmployeeRole::employeeRoles();
        return $this->belongsToMany(Company::class)->withPivot('employee_role')->wherePivotIn('employee_role', $normalEmployeeRoles);

When using with('workingCompanies') the attribute $roll will always be null.

Therefore, you load all roles with :

EmployeeRole::employeeRoles();

So change your relation to :

public function workingCompanies() {
        return $this->belongsToMany(Company::class);

and then

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role, $company) {
    $query->wherePivot('employee_role',$role);
})->get();

I just tried, the right answer is

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role) {
                $query->where('employee_role',$role);
            })->get();

We use 'where' instead of 'wherePivot'. Everything keeps the same for me. Thanks for your advice. I'm a newbie here, so, which answer do I mark correct ?

Yours, as it's the one that is correct ;)

Hi chinhle, one more question from my side, in your right answer

$query->where('employee_role',$role)

the column in this where clause is all the pivot table column names, isn't it? like if I want to filter with company id, I should use 'company_id'. Thanks

In this specific case ? If u want to filter by company_id, I guess we should do something like Company::find($company_id)->employees->wherePivot(‘employee_role’,$role)

Hi Chinhle, Thanks, I am just wondering about the column name in the sub clause " $query->where('employee_role',$role);" in your code.

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role) { $query->where('employee_role',$role); })->get();

the first parameter in where('employee_role',$role), should come from the pivot table as my understanding. I agree with your way to filter the employees belong to one company in your reply. But if I write like below, I could write

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role) { $query->where('company_id', $id); // column for the pivot table })->get();

or

$normalEmployees = Employee::whereHas('workingCompanies', function ($query) use ($role) { $query->where('id',$id); //column name from company table })->get();

I tried both are working. Hope you agree with me.

Check out these examples from the Laravel 10 documentation.

https://laravel.com/docs/10.x/eloquent-relationships#filtering-queries-via-intermediate-table-columns

return $this->belongsToMany(Role::class)
                ->wherePivot('approved', 1);
 
return $this->belongsToMany(Role::class)
                ->wherePivotIn('priority', [1, 2]);
 
return $this->belongsToMany(Role::class)
                ->wherePivotNotIn('priority', [1, 2]);
 
return $this->belongsToMany(Podcast::class)
                ->as('subscriptions')
                ->wherePivotBetween('created_at', ['2020-01-01 00:00:00', '2020-12-31 00:00:00']);
 
return $this->belongsToMany(Podcast::class)
                ->as('subscriptions')
                ->wherePivotNotBetween('created_at', ['2020-01-01 00:00:00', '2020-12-31 00:00:00']);
 
return $this->belongsToMany(Podcast::class)
                ->as('subscriptions')
                ->wherePivotNull('expired_at');
 
return $this->belongsToMany(Podcast::class)
                ->as('subscriptions')
                ->wherePivotNotNull('expired_at');