Laravel 4: pivot table example (attach and detach) - developed.be

What’s a pivot table?

A pivot table is a database table that only exists to serve a many-to-many relationship. Say you have a table “customer” and a table “drinks”. If you want to know which customer ordered which drink you have to create a pivot table customer_drinks(customer_id, drink_id).

Laravel can handle these tables (semi)automatically.

Define the pivot table in Laravel

If you want to define the pivot table in Laravel, you have to create a belongsToMany relationship. Example:

class Customer extends \Eloquent {    
    public function drinks()
    {
        return $this->belongsToMany('Drink', 'customer_drinks', 'customer_id', 'drink_id');
    }
}

The first argument in belongsToMany() is the name of the class Drink, the second argument is the name of the pivot table, followed by the name of the customer_id column, and at last the name of the drink_id column.

The second, third and fourth argument are not mandatory. Laravel can figure the names out itself, but I to avoid mistakes I always explicitly name them.

Create a record in the pivot table

Use the “attach” method to create a record in the pivot table. In this example we attach a drink to a customer, the drink and the customer already exist in the database.

$customer = Customer::find($customer_id);
$customer->drinks()->attach($drink_id); //this executes the insert-query

Remove a record in the pivot table

To remove the record, use “detach”. This removes just the pivot-table record, not the drink record.

$customer = Customer::find($customer_id);
$customer->drinks()->detach($drink_id); //this executes the delete-query on the pivot table

If you call detach() without an argument, it will delete all records that belong to that customer.

Attach an extra column to the pivot table

The above example is pretty basic. What if the pivot table contains more than two columns, eg: what if there’s a boolean column to indicate wheter the customer already got their drinks?

Say we have the table customer_drinks(customer_id, drink_id, customer_got_drink [bool])

$customer = Customer::find($customer_id);
$customer->drinks()->attach($drink_id, array('customer_got_drink', 1)); //this executes the insert-query with customer_got_drink = 1

The detach method is exactly the same as in the first example.

Select the pivot row

There are 2 ways that work to load the pivot data

Defining the pivot in the Model

The most clean way is to define the pivot(s) in the model.

Laravel kinda automatically loads the pivot data. Comparing to our first example, the model has to look like this:

class Customer extends \Eloquent {    
    public function drinks()
    {
        return $this->belongsToMany('Drink', 'customer_drinks', 'customer_id', 'drink_id')->withPivot('customer_got_drink');
    }
}

The argument in the withPivot() method is the column-name of the extra data we want to add.

To get a list of drinks the customer, execute this code:

foreach($customer->drinks as $drink){
    print '<li>' . $drink->name . ' ' . $drink->pivot->customer_got_drink;
}

Notice that we don’t add () after $customer->drinks. Alternatively we can execute this:

foreach($customer->drinks()->select('name', 'pivot_customer_got_drink')->get() as $drink){
    print '<li>' . $drink->name . ' ' . $drink->pivot->customer_got_drink;
}

The result (and the SELECT query) will be the same in both cases.

You can define more pivot-columns as arguments in the withPivot() method.

return $this->belongsToMany('Drink', 'customer_drinks', 'customer_id', 'drink_id')->withPivot('customer_got_drink', 'weight', 'some_other_column');

Manually selecting the pivot column

Say you don’t always want to load the pivot-column, but just once. You accomplish the same result without using the withPivot() method in the model, like this:

foreach($customer->drinks()->select('name', 'customer_got_drink')->get() as $drink){
    print '<li>' . $drink->name . ' ' . $drink->customer_got_drink;
}

This will again have the same result and execute the same query as in the previous examples, but with different column-names.

Extra foreign key column in pivot table

But what if the extra column is a foreign key to some other table?  Let’s say we’re in a casino where customers regularly switch chairs and we want to register at which chair the drink was ordered.

Say: customer_drinks(customer_id, drink_id, customer_got_drink, chair_id)

And: chair(chair_id, chair_name)

The method withPivot(‘chair_id’) won’t automatically join with table the chair table, so we don’t have access to the chair_name column that way. We have to explicitly join with table chair.

class Customer extends \Eloquent {    
    public function drinks()
    {
        return $this->belongsToMany('Drink', 'customer_drinks', 'customer_id', 'drink_id')
                    ->withPivot('customer_got_drink', 'chair_id');
                    ->join('chair', 'chair_id', 'chair.id');
                    ->select('drink_id', 'customer_id', 'pivot_customer_got_drink', 'chair.name AS pivot_chair_name'); //this select is optional
    }
}

View the actual queries

It’s helpful to actually view the queries Laravel executes. From Laravel4 you have to install a profiler [packagist.org]. Add it like any package (see the readme). Set debug to true in app/config.php.

In my previous blog you could read why I chose Laravel as framework.


Rss Comments

15 comments

  1. nice! thanks!

    #1 Автор
  2. Syntax error should be ));

    $customer->drinks()->attach($drink_id, array(‘customer_got_drink’, 1);

    #2 Kuba
  3. Hey Robin!
    Thanks for this blog. It has helped me a lot in working with pivot tables in laravel.
    I have however gotten an issue with the extra foreign key in the pivot table. I added the ‘join’ line as you had showed but I got a SQL error. I changed it to “join(‘chair’, ‘chair_id’, ‘=’, ‘chair_id’)”. I didnt include the semicolons at the end though. I no longer get any errors but then my variable holding the results displays null. I am however able to display the other fields but not the field of the parent table (linked by extra foreign key).

    #3 jMichael
  4. Thanx man! This was exactly what I needed!

    #4 raice
  5. Hello Robin,

    Thanks a lot for this article, but i’ve some doubts, what is the best way to update a pivot table with more attributes than the foreign keys?

    #5 Pumu
  6. With Laravel Generator you can avoid a lot of this code: https://github.com/JeffreyWay/Laravel-4-Generators#pivot-tables

    #6 Tom Sarduy
  7. Started using laravel could not figure out how to implement pivot tables so ended up with adding an extra model for the pivot table. It kept bugging me so ended up here and it finally work’s! Thanks a lot!

    #7 rovadeka
  8. thanks for the example, but there is a little error : $customer->drinks()->attach($drink_id, array(‘customer_got_drink’, 1)); it should be $customer->drinks()->attach($drink_id, array(‘customer_got_drink’ => 1));

    #8 Jeremy
  9. Thanks for the article and yes, there are a couple syntax errors. The semicolons at the end of each line in the ->join code should not be there and Jeremy !!! you the man! I had been struggling for a few days over this and your comment saved me. Thank you!

    #9 Christopher
  10. For whatever reason, when I detach, it removes all the records rather than just the one that has the extra pivot set to a particular value. I’m still searching for a solution for this. If anyone can point me in the right direction I would appreciate it.

    I have a role_user_account table where a user might have one or more roles that apply to a specific account. When I attempt to detach a role for an account, it detaches all of the roles for that user on all accounts. I need to detach a role by account_id.

    #10 Christopher
  11. Robin,

    Just wanted to offer a BIG THANK-YOU. You have explained how I can implement a many-many relationship in Laravel succinctly.

    I wish the Laravel API was as helpful!

    Great job…………. you are a star!

    #11 Stephan Jusypiw
  12. This is a fantastic article! Sadly Eloquent’s documentation is woeful when it comes to the use of pivot tables.
    One little typo though:

    $drink->pivot_customer_got_drink

    should be:

    $drink->pivot->customer_got_drink

    #12 omar
  13. thank alot :)

    #13 soleha
  14. but can i print the chair attributes?

    #14 Husni
  15. I like your cat

    #15 James

Leave a comment