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

Working with the pivote table data was not clear to me initially - especially deleting from the pivote 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 actions to cascade on delete and update. This will force any update to the parent table to cascade down the data in the pivote 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`)

  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)

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`) 
  CONSTRAINT `tag_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) 

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->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. Lavael 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);

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.

comments powered by Disqus