Working with Data in Pivot Tables using Laravel 4 Eloquent ORM (SELECT, UPDATE and DELETE)

Working with the pivot table data was not clear to me initially - especially deleting from the pivot table. I thought Laravel would handle this since it already handles the inserting and updating of the data. But it turns out this needs to be handled at the database level.

When setting up your pivot table, you need to set the referential actionsto cascade on delete and update. This will force any update to the parent table to cascade down the data in the pivot table.

Below is a simplified example using a blog with posts and tags.

Database Tables

CREATE TABLE `posts` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `body` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `post_tag` (
  `post_id` int(11) unsigned DEFAULT NULL,
  `tag_id` int(11) unsigned DEFAULT NULL,
  KEY `tag_post_id` (`post_id`),
  KEY `tag_tag_id` (`tag_id`),
  CONSTRAINT `tag_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tag_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

Many-to-Many Model Relationship

Now within your Post model you need to setup your many-to-many relationship which tells Laravel to use your pivot table post_tags since many posts will have many different tags.

class Post extends Eloquent {
   public function tags()
   {
      return $this->belongsToMany('Tag');
   }
}

INSERT and UPDATE using the Relationship

We’re going to assume there are already some tags in our tags table so we can add them to a new post. (note: updating an existing post works the same - see commented code below) We’re going to use the sync() method which according the Laravel’s docs is:

You may also use the sync method to attach related models. The sync method accepts an array of IDs to place on the pivot table. After this operation is complete, only the IDs in the array will be on the intermediate table for the model

$post = new Post; // to update: $post = Post::find(1);
$post->title = Input::get('title');
$post->body = Input::get('body');
$post->save();

$post->tags()->sync(array(1,2,3,4)); // tag ids

SELECT the Data

Now to read those tags from the relationship you just need to request the tag field like it was in the posts table. Laravel will do the SQL JOIN for you.

$post = Post::find(1);
foreach($post->tags as $tag)
{
   echo $tag;
}

DELETE the Relationship

This is where those foreign key constrains are going to come in. We’re not actually going to directly delete the tags from the post_tag table. We’re only going to delete records from that table when a post is delete. So for example a post is deleted:

$post = Post::find(1);
$post->delete();

This will trigger all related data within the post_tag table to be deleted as well. Pretty cool and very convenient!

Always open to feedback or questions, so please let me know bellow in the comments.