Laravel Many to Many - 3 models

1
votes

Some help with many to many relationships in Laravel:

Using the example for roles and users - basically:

  • a table for all the roles
  • a table for the users
  • and table with user_id and role_id.

I want to add to the third table, eg Year. basically the pivot table will have user_id, role_id and year_id.

I want to be able to make a query to pull for example all users assigned a specific role in a specific year. Eg All users with role_id = 2, and year_id = 1.

Any help will be appreciated

3
laravel
why u want to use third table for year id? why can't u put year_id in users table or table with user_id and role_id? what are the relationships? – Mr. Sensitive
basically, you can have one user assigned different roles in different years, meaning in year 1, a user can have role a, and in year 2, he has role b. So i want to see all users assigned role a in year 1 for example – Mohamed Omar

3 Answers

1
votes

Before answering, I would like to suggest you not to put year on database like this. All your tables should have created_at and updated_at which should be enough for that.

To filter users like you want. You could do this:

// This queries all users that were assigned to 'admin' role within 2013.
User::join('role_users', 'role_users.user_id', '=', 'users.id')
    ->join('roles', 'roles.id', '=', 'role_users.role_id')
    ->where('roles.name', '=', 'admin')
    ->where(DB::raw('YEAR(role_users.created_at)', '=', '2013')
    ->get();

This example may not be the precise query you are looking for, but should be enough for you to come up with it.

0
votes

The best way to achieve a three way relation with Eloquent is to create a model for the table representing this relation. Pivot tables is meant to be used for two way relations.

You could have then a table called roles_users_year which could have data related to this 3 way relation like a timestamp or whatever...

0
votes

A very late answer to a very old question, but Laravel has supported additional intermediate (pivot) table columns of at least Laravel 5.1 judging from the documentation, which hasn't changed at least through Laravel 6.x.

You can describe these extra columns when defining your many-to-many relationship:

return $this->belongsToMany(Role::class)->withPivot('column1', 'column2');

or in your case, the below would also do the job:

return $this->belongsToMany(Role::class)->withTimestamps();

which you can then access via the pivot attribute on your model:

$user = User::find(1);

foreach ($user->roles as $role) {
    echo $role->pivot->created_at;
}

Note that the pivot attribute is on the distant relationship model (a single Role) and not on the relationship itself.

To get all the Roles assigned to Users in any given year, you might create a special relationship:

// User.php

public function rolesInYear($year) {
    return $this->belongsToMany(Role::class)
        ->wherePivot('created_at', '>=', Carbon::create($year))
        ->wherePivot('created_at', '<', Carbon::create($year + 1));
}