Contoh Penerapan Pivot Table Pada Laravel

Hari ini kita membahas tentang fitur Laravel yang sangat berguna tetapi pada awalnya mungkin sulit untuk dipahami. Tabel pivot (Pivot Tables) adalah contoh tabel perantara dengan hubungan antara dua tabel “utama” lainnya atau istilahnya Many to Many Relationships.

Pada tutorial ini saya akan mengambil contoh antara user dan role, dimana satu user dapat memiliki banyak role dan sebaliknya satu role dapat dimiliki oleh banyak user, maka kita membutuhkan satu tabel tambahan bernama role_user, nah table role_user inilah yang disebut sebagai pivot table atau table perantara.

Aturan penulisan pivot table yang benar adalah sesuai abjad masing-masing table yang ingin kita hubungkan, misalnya Role dan User diawali dengan huruf “R” dan “U” maka sesuai abjad adalah “R” yang lebih dulu daripada huruf “S”, maka tabel penghubungnya (Pivot Table) bisa kita buat menjadi role_user.

Jadi, di bawah ini adalah struktur database yang akan kita terapkan:

users
– id
– name
– email
– password

roles
– id
– name

role_user
– user_id
– role_id

Kita sudah mengetahui struktur table yang ingin kita buat, selanjutnya buatlah migrasi database terlebih dahulu.

//users table
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->unique();
        $table->string('password');
        $table->timestamps();
    });
}

//roles table
public function up()
{
    Schema::create('roles', function (Blueprint $table) {
        $table->id();
        $table->string('name', 30)->unique();
    });
}

//role_user table harus sesuai abjad lebih dulu
public function up()
{
    Schema::create('role_user', function (Blueprint $table) {
        $table->id();
        $table->bigInteger('role_id')->unsigned();
        $table->foreign('role_id')
            ->references('id')->on('roles')
            ->onDelete('cascade');

        $table->bigInteger('user_id')->unsigned();
        $table->foreign('user_id')
            ->references('id')->on('users')
            ->onDelete('cascade');
    });
}

app/Models/User.php

protected $table = 'users';
public function roles()
{
    return $this->belongsToMany(App\Models\Role::class);
}

app/Models/Role.php

protected $table = 'roles';
public $timestamps = false;
public function users()
{
    return $this->belongsToMany(App\Models\User::class);
}

app/Models/RoleUser.php

protected $table = 'role_user';
public $timestamps = false;

Controller

use Illuminate\Support\Facades\DB;

public function create()
{
    $roles = Role::select('id', 'name')->get();
    return view('users.create', compact('roles'));
}

public function store(Request $request)
{
    $request->validate([
        'name'=>'required|string|max:100',
        'email'=> 'required|string|email|max:100|unique:users',
        'password' => 'required|string|min:6'
    ]);

    $role = $request->roles;

    DB::beginTransaction();
    try {
        $user = new User;
        $user->name = ucwords($request->name);
        $user->email = $request->email;
        $user->password = Hash::make($request->password);
        $user->save();
        $user->roles()->attach($role);//create
        DB::commit();//simpan db jika tidak ada error
        return redirect($request->url)->with('success', 'User baru berhasil dibuat!');
    } catch (\Exception $e) {
        DB::rollback();//rollback db jika terjadi error
        return redirect($request->url)->with('danger', $e->getMessage());
    }
}

public function edit(Request $request, $id)
{
    $user = User::findOrFail($id);
    $roles = Role::select('id', 'name')->get();
    return view('users.edit', compact('user','roles'));
}

public function update(Request $request, $id)
{
    $request->validate([
        'name'=>'required|string|max:100',
        'email'=> 'required|string|email|max:100|unique:users,email,'.$id.''
    ]);

    DB::beginTransaction();
    try {
        $role = $request->roles;
        $user = User::find($id);
        $user->name = ucwords($request->name);
        $user->email = $request->email;
        if($request->filled('password'))
        {
            $user->password = Hash::make($request->password);
        }

        $syncUserRole = $user->roles()->sync($role);//update

        $changed = false;

        if ($user->isDirty()) {
            $user->save();
            $changed = true;
        }

        if (collect($syncUserRole)->flatten()->isNotEmpty()) {
            $changed = true;
        }

        DB::commit();

        if($changed){
            return redirect($request->url)->with('success', 'Perubahan berhasil disimpan!');
        } else {
            return redirect($request->url)->with('info', 'Tidak ada perubahan!');
        }
    } catch (\Exception $e) {
        DB::rollback();
        return redirect($request->url)->with('danger', $e->getMessage());
    }
}

resources/views/users/create.blade.php

<div class="form-group row">
    <label class="col-xl-3 col-lg-3 col-form-label">Role</label>
    <div class="col-lg-9 col-xl-9">
        <select multiple="multiple" class="form-control form-control-lg" id="roles" name="roles[]">
        @foreach($roles as $role)
            <option value="{{ $role->id }}" {{ ($role->id == old('roles')) ? 'selected' : '' }}>{{ $role->name }}</option>
        @endforeach 
        </select>
    </div>
</div>

resources/views/users/edit.blade.php

<div class="form-group row">
    <label class="col-xl-3 col-lg-3 col-form-label">Role</label>
    <div class="col-lg-9 col-xl-9">
    <select multiple="multiple" class="form-control form-control-lg" id="roles" name="roles[]">
        @foreach($roles as $role)
            <option value="{{ $role->id }}" {{ $user->roles->containsStrict('id', $role->id) ? 'selected' : '' }}>{{ $role->name }}</option>
        @endforeach 
    </select>
    </div>
</div>

Sekian, Semoga membantu…