Laravel Pivot Tables

Database tables are often related to one another. Laravel Eloquent supports serveral different types of relationship, including Many-To-Many. Many-to-many relationships are defined by writing a method that returns the result of the belongsToMany method, this relationship type is more complicated than hasOne and hasMany,

  1. Migrations
  2. Relationships
  3. Pivot Table usage example

Creating Pivot Table Migrations

public function up()
{
    Schema::create('table_example', function (Blueprint $table) {

      $table->unsignedInteger('table1_id');
      $table->foreign('table1_id')->references('id')->on('table1');

      $table->unsignedInteger('table2_id');
      $table->foreign('table2_id')->references('id')->on('table2');

    });
}

public function down()
{
    Schema::dropIfExists('table_example');
}

Creating Pivot Table Relationships

Now that the new table exists it is important to include the relationships to the models of the associated tables. The relationship type is called many-to-many, this means both tables can have many of one another. It is important to specify the model, table name, column of other model and column of current model id. You may also want to create a Model for the new table for deleting columns where necessary:

// Has Many Table2
public function tabletwo()
{
  return $this->belongsToMany('App\Tabletwo', 'table_example', 'table2_id', 'table1_id');
}

// Has Many Table1
public function tableone()
{
  return $this->belongsToMany('App\Tableone', 'table_example', 'table1_id', 'table2_id');
}

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class TableExample extends Model
{
  //Table Name
  protected $table = 'table_example';
}

Pivot Table usage example

Now that the tables and Models are ready, we are able to save the data within the help of calling the relationship. Storing or deleting the data will not work unless the relationship is called through the model. This method can be achieved using either Tableone::find($tableone_id) or Tableone::findOrFail($tableone_id).

$tableone = Tableone::findOrFail($tableone_id);
$tableone->tabletwo()->attach($table2_id);

// Removing the Data using the model relationship
$tableone = Tableone::findOrFail($tableone_id);
tableone->tabletwo()->detach($table2_id);



// Removing the Data using the pivot table model
use App\AnnotationLabel;
AnnotationLabel::where('label_id', $id)->delete();

References