Pivot tables can be confusing and a little hard to wrap your head around at first. In this quick article we are going to dive into what a pivot table is, how to create one and finally how to use the pivot table. Let's dive in!
A pivot table is used to connect relationships between two tables. Laravel provides a Many To Many relationship where you can use a pivot table.
In the example next, we will review how a users table and a roles table are joined together with a user_roles pivot table. Pivot tables allow you to (in this case) add multiple roles to a user.
Let's create two tables which we need to use the Many To Many relationship for.
- Table:
users - Table:
roles - (Pivot) Table:
user_roles
The users table will look something like this:
id(INT)name(VARCHAR)
The roles table will look like this:
id(INT)name(VARCHAR)
Now our pivot table user_roles will look like this:
user_id(INT)role_id(INT)
So now that we've gone over the structure of the two tables and our pivot table, let's dig into how to use them!
The migration should look like this, it establishes the proper columns and foreign key relations to both the users and roles table. The migration will also set both user_id and role_id as the primary keys so there cannot be duplicates with both the same user_id and role_id.
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateUserRolesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('user_roles', function (Blueprint $table) {
$table->integer('user_id')->unsigned()->index();
$table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
$table->integer('role_id')->unsigned()->index();
$table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
$table->primary(['user_id', 'role_id']);
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('user_roles');
}
}We will open our User.php and Role.php models and define the relations needed in both models.
<?php
namespace App\Models;
use App\Models\Role;
use Illuminate\Notifications\Notifiable;
use Illuminate\Foundation\Auth\User as Authenticatable;
class User extends Authenticatable
{
use Notifiable;
/*
* The roles that belong to this user
*/
public function roles()
{
return $this->belongsToMany(Role::class);
}
}<?php
namespace App\Models;
use App\Models\User;
use Illuminate\Database\Eloquent\Model;
class Role extends Model
{
/*
* The users that belong to this role
*/
public function users()
{
return $this->belongsToMany(User::class);
}
}If you want to define the custom model that represents the pivot or intermediate table of your relation, you can call the using method when defining the relationship. Note that custom Many To Many pivot models should extend the Illuminate\Database\Eloquent\Relations\Pivot class. For example, we want to define a Role which uses a custom UserRole pivot model:
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Relations\Pivot;
class UserRole extends Pivot
{
//
}Let's assume our User object has many Role objects that it is related to. After accessing this relationship, we may access the intermediate table using the pivot attribute on the models.
$user = App\Models\User::first();
foreach ($user->roles as $role) {
echo $role->name; // This will echo out the role name
}Notice that each
Rolemodel we retrieve is automatically assigned apivotattribute. This attribute contains a model representing the intermediate table, and may be used like any other Eloquent model.
As mentioned above, attributes from the intermediate table may be accessed on models using the pivot attribute. However, you can customize the name of this attribute to better reflect its purpose within your application.
For example, if your application contains users that may subscribe to multiple podcasts, you probably have a many-to-many relationship between users and podcasts. If this is the case, you may wish to rename your intermediate table accessor to subscription instead of pivot. This can be done using the as method when defining the relationship:
public function podcasts()
{
return $this->belongsToMany(Podcast::class)
->as('subscriptions');
}Once this is done, you can access the intermediate table data using the customized name from above:
$users = App\Models\User::with('podcasts')->get();
foreach ($users->flatMap->podcasts as $podcast) {
echo $podcast->name; // This will echo out the podcast name
}You may be asking what
flatMapdoes in the above code example. It's a Collection helper. Eloquent returns aCollectionon a relation by default. We useflatMapin the example to iterate through the collection and pass each value to the given callback. The callback is free to modify the item and return it, thus forming a new collection of modified items. Then, the array is flattened by a level (which is what we want in this case).
$user = App\Models\User::first(); // John Doe
$role = App\Models\Role::first(); // Admin
$user->roles()->attach($role->id);You can pass an array of IDs to the attach method to add multiple roles to a user.
$user = App\Models\User::first(); // John Doe
$roles = App\Models\Role::all()->pluck('id'); // Admin, User
// Adds MULTIPLE roles to the user
$user->roles()->attach($roles);$user = App\Models\User::first(); // John Doe
$role = App\Models\Role::first(); // Admin
$user->roles()->detach($role->id);This works the same as above when adding a role but you can pass an array of IDs to the detach method to remove multiple roles from a user.
$user = App\Models\User::first(); // John Doe
$roles = App\Models\Role::all()->pluck('id'); // Admin, User
// Removes MULTIPLE roles to the user
$user->roles()->detach($roles);There's a method called sync that accepts new vales as an array of IDs and will take care of the syncing of roles. The result is that no matter what values were in the table prior to running sync, after the code is run there will only be these role IDs attached to the user. It will remove any not in this list that are in the database and add any that aren't in the database but that are in this list.
$user = App\Models\User::first();
$roles = App\Models\Role::all()->pluck('id');
// $roles will provide an array of role IDs that will be synced to the user
$user->roles()->sync($roles);Sometimes you may want to add additional parameters when attaching a role to a user. Let's take a look at an example on how to do this. In this example, assume the Administrator is assigning a user a new role but they want to specify who it was added by (just a string for simplicity sake, assume added_by is a column on the user_roles table alongside user_id and role_id).
$user = App\Models\User::first(); // John Doe
$role = App\Models\Role::find(2); // User
$user->roles()->attach($role->id, ['added_by' => 'Braunson']);There's a bunch more things you can do with relations and they are documented in the Laravel documentation. The goal of this write-up was to establish a basic understanding and usage with pivot tables in Laravel.