Many to Many Relationship - Migrations

Nov 21, 2016 migration manytomany database relationships
WhatsApp
The basis of this tutorial is to establish a ‘many to many’ relationship in a database and the eloquent model. A common situation where this would occur could be on a blog where posts have associated tags. The reason being is – a post can have many Tags and a tag can have many posts. What wouldn’t be a ‘many to many’ relationship an example could be an author could have have many posts but a post can only have one author.

A relationship like this would need three tables. One for Posts, Tags and the last to link the Posts and Tags table togther, sometimes called a Pivot Table. The naming convention for a pivot table usually is the two table names joined with an underscore in alphabetical order, ‘posts_tags’. The table will hold foreign keys from the Posts and Tags table. The overall set up could look something like

Posts
Id
Name
Content

Tags
Id
Name

Pivot
posts_id
tags_id

Now let’s translate this using Laravel’s migration.


Php artisan make migration:



Schema::create(posts, function(Blueprint $table) {
$table->increments('id');
$table->string('name');
$tabl->text(‘content’);
$table->timestamps();
});

Schema::create('tags', function(Blueprint $table) {
$table->increments('id');
$table->string('name');
$table->timestamps();
});

//pivot table
Schema::create('posts_tags', function(Blueprint $table) {
$table->integer('tags_id')->unsigned()->index();
$table->foreign('tags_id')->references('id')->on('tags')->onDelete('cascade');

$table->integer('posts_id')->unsigned()->index();
$table->foreign('posts_id')->references('id')->on('posts')->onDelete('cascade');
$table->timestamps();
});


We’ve covered the code for each of the tables but let’s look at how we reference foreign keys.



$table->integer('posts_id')->unsigned()->index();
$table->foreign('posts_id')->references('id')->on('posts')->onDelete('cascade');


First we create the column for the foreign key of unsigned type because keys can’t be negative, and also apply the index method which is needed for a foreign key.

Next apply the foreign key reference to the column (‘posts_id’) and what key it references (‘id’), in what table (‘posts’). The cascade argument for onDelete means that any data deleted from the posts or tags table, they will affect the pivot table. Which is handy as you don’t want to be having data lingering around in your pivot table if you have deleted a post or tag.

Now we’ve set up our migrations for a many to many relationship. We’ll continue on to create the models for each table in the next part.

If you’ve forgotton, migrate the tables with the command


Php artisan migrate

If you found the following info helpful, I'm happy to accept any donations of the following cryptocurrencies.

  • Bitcoin - 17DTiPExzP9StqveW428acEyB4mVMfKbiK
  • Ethereum - 0x87B8307FD20dc90cc05c94905Ec593134D32B6FF
  • Litecoin - LZMiz5U5sVq9doMLYE3gfLJrxCQDKuyCmU
  • Neo - AXv71WB38ajc1KUUEnxQKhynLLPc4BapVb