CODEWITHSUNDEEP
mysqllaraveleloquentforeign-keys
SaltyPotato
SaltyPotato

Reputation: 322

How can I "filter" a ManyToMany child in Laravel Eloquent

First some context

I am working on a corporate application, in this application a user can have access to multiple companies. The same user can be assigned to one employee in each company. To prevent the user from creating multiple user accounts, the user will be "connected" to a company and "company employee" via pivot tables.

The problem

I have a Company model in laravel with a users() attribute, this works as expected (because it returns all the users connected to the business via the pivot table).

In my User model I have an employee() attribute, this returns all the employees linked to the user via the pivot table, including the employees that are not linked to the requested business / do not have the same business_fk value as the requested Business ID.

I need to get the single employee that is connected to the requested business and user via the pivot tables.

So I think I need to filter the User -> Employee relation by the business_fk key in the users() attribute in the Company model, but I don't know how.

Model: Company

public function users()
{
    return $this->belongsToMany(User::class, 'user_employee_pivot', 'employee_fk', 'user_fk')
    ->withPivot('ID');
}

Model: User

public function employee()
{
    return $this->belongsToMany(Employee::class, 'user_employee_pivot', 'user_fk', 'employee_fk')
    ->withPivot('ID');
}

Simplified table structure

TABLE users
ID           PK INT
username     STRING

--------

TABLE company
ID      PK INT
name    STRING

--------

TABLE employee
ID          PK INT
fullname    STRING
company_fk  FK INT (referencing to company table PK)

--------

TABLE user_business_pivot
ID            PK INT
user_fk       FK INT (referencing to user table PK)
company_fk    FK INT (referencing to company table PK)

--------

TABLE user_employee_pivot
ID            PK INT
user_fk       FK INT (referencing to user table PK)
employee_fk   FK INT (referencing to employee table PK)

If I'm not clear enough, please let me know. I will edit my question accordingly.

Upvotes: 0

Views: 79

Answers (1)

mrhn
mrhn

Reputation: 18926

Employees you can fetch, by filtering the relationship, since it is belongsToMany employee should be employees.

public function getEmployeesByCompany(User $user, Company $company) {
    return $user->employees()->where('company_fk', $company->id)->get();
}

This method could easily be done on the user model or similar, this is just an example.

Upvotes: 1

Related Questions