While designing database structure, you may get to know that some tables are related to one another. For example in a blog application, a one country have many states and states have many cities. In a MySQL database tables, we add one table's id to another table or create pivot table to create relationship between tables.
Laravel provides eloquent relationship which provides powerful query builders. In Laravel, eloquent relationships are defined in model classes. Laravel eloquent provides easy ways to create common relationships:
- one to one
- one to many
- many to many
- has one of many
- has one through
- has many through
- one to one polymorphic
- one to many polymorphic
- many to many polymorphic
In this article, we will learn how to create many to many eloquent relationship between two models. This is slightly complicated than one to one and one to many relationship. For example, One Role may have more than one Permission and same way, one Permission can be assigned to more than one Role.
Example:
In this example, we assume that you have created fresh Laravel application. We also assume that you have confiured database connection.
Database tables
To create many to many relationship, we need three database tables: roles, permissions and permission_role. The permission_role table is created from the alphabetical order of the related model names and contains two fields: role_id and permission_id. The table is used as intermediate table between roles and permissions.
Migration
we need to create three migration table. Run the following three commands into Terminal to create migration classes at database/migrations directory.
php artisan make:migration create_roles_table
php artisan make:migration create_permissions_table
php artisan make:migration create_permission_role_table
Below are the migration table fields for these table:
roles migration
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('roles', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->timestamps();
});
}
permissions migration
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('permissions', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->timestamps();
});
}
permission_role migration
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('permission_role', function (Blueprint $table) {
$table->integer('role_id');
$table->integer('permission_id');
$table->timestamps();
});
}
Run the migrate command to create tables into database.
php artisan migrate
Model
Laravel model located at app/Models directory. Create model classes for these tables using following Artisan commands one by one into Terminal.
php artisan make:model Role
php artisan make:model Permission
Now, you need to create permissions method in the Role model that will return belongsToMany method of Illuminate\Database\Eloquent\Model class.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Role extends Model
{
use HasFactory;
/**
* The roles that belong to the user.
*/
public function permissions()
{
return $this->belongsToMany(Permission::class);
}
}
The eloquent will automatically checks role_id and permission_id fields in the permission_role pivot table which relates to id field of both tables. You can also define pivot table into relationship method as second parameter.
return $this->belongsToMany(Permission::class, 'permission_roles');
To define reverse relationship, i.e., if you already have permission and you want to find all roles of the permission by relationship, you need to create roles method into Permission model.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Permission extends Model
{
use HasFactory;
/**
* The roles that belong to the permission.
*/
public function roles()
{
return $this->belongsToMany(Role::class);
}
}
Now, if your pivot table also have some extra fields which are required when you retrieve relationship data, then you need to specify them when defining the relationship:
return $this->belongsToMany(Role::class)->withPivot('is_active', 'created_at');
Route
In routes/web.php file, we have added new route for relationship testing.
<?php
use Illuminate\Support\Facades\Route;
use App\Http\Controllers\PermissionController;
Route::get('/permission', [PermissionController::class, 'index']);
Controller
As we have added route, also create PermissionController with following command.
php artisan make:controller PermissionController
Find all permissions for the specific role
Open the controller at app/Http/Controllers/PermissionController and create index method.
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$permissions = Role::find(1)->permissions;
dd($permissions);
}
This will return all permissions of the the role with the id of 1. As all the relationship serve as query builder, you may also constraints in permissions() method.
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$permissions = User::find(1)->permissions()
->orderBy('created_at' 'desc')
->get();
dd($permissions);
}
Find all roles for the selected permission
Now suppose you want to find all the roles which are assigned the permission, i.e., reverse relationship, here is how to access user record.
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$roles = Permission::find(1)->roles;
dd($roles);
}
You can even eager load the relationship data while quaring parent model data using with() methdod.
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
$roles = Role::with('permissions')->get();
foreach($roles as $role) {
echo($role->permissions);
}
}
I hope this will help you to understand many to many eloquent relationship.
