Today I want to talk about a feature of Laravel which is really useful but can be potentially difficult to understand at first. Pivot table is an example of intermediate table with relationships between two other “main” tables.
Real-life example of pivot tables
In official documentation they show the example of User-Role relationships, where user potentially can belong to several roles, and vice versa. So to make things clearer – let’s take another real-life example: Shops and Products.
Let’s say a company has a dozen of Shops all over city/country and a variety of products, and they want to store the information about which Product is sold in which Shop. It’s a perfect example of many-to-many relationship: one product can belong to several shops, and one shop can have multiple products.
So here’s a potential database structure:
shops
– id
– name
products
– id
– name
product_shop
– product_id
– shop_id
The final table in the list – product_shop is called a “pivot” table, as mentioned in the topic title. Now, there are several things to mention here.
- Name of the pivot table should consist of singular names of both tables, separated by undescore symbole and these names should be arranged in alphabetical order, so we have to have product_shop, not shop_product.
- To create a pivot table we can create a simple migration with artisan make:migration or use Jeffrey Way’s package Laravel 5 Generators Extended where we have a command artisan make:migration:pivot.
- Pivot table fields: by default, there should be only two fields – foreign key to each of the tables, in our case product_id and shop_id. You can add more fields if you want, then you need to add them to relationship assignment – we will discuss that later.
Models for Many-to-Many Relationships: BelongsToMany
Ok, we have DB tables and migrations, now let’s create models for them. The main part here is to assign a many-to-many relationship – it can be done from either of “main” tables models.
So, option 1:
app/Shop.php:
class Shop extends Model { /** * The products that belong to the shop. */ public function products() { return $this->belongsToMany('App\Product'); } }
Or option 2:
app/Product.php:
class Product extends Model { /** * The shops that belong to the product. */ public function shops() { return $this->belongsToMany('App\Shop'); } }
Actually, you can do both – it depends on how will you actuall use the relationship in other parts of the code: will you need $shop->products or more likely to query $product->shops, or both.
Now, with such declaration of relationships Laravel “assumes” that pivot table name obeys the rules and is product_shop. But, if it’s actually different (for example, it’s plural), you can provide it as a second parameter:
public function products() { return $this->belongsToMany('App\Product', 'products_shops'); }
Moreover, you can specify the actual field names of that pivot table, if they are different than default product_id and shop_id. Then just add two more parameters – first, the current model field, and then the field of the model being joined:
public function products() { return $this->belongsToMany('App\Product', 'products_shops', 'shops_id', 'products_id'); }
One of the main benefits here: you don’t need to create a separate model for ProductShop – you will be able to manage that table through pivot commands, we will discuss that right away.
Go to QuickAdminPanel.com
Managing Many-to-Many Relationships: attach-detach-sync
So, we have tables, and we have Models ready. Now, how do we actually save the data with a help of our two Models instead of the third intermediate one? Couple of things here.
For example, if we want to add another product to the current shop instance, we use relationship function and then method attach():
$shop = Shop::find($shop_id); $shop->products()->attach($product_id);
The result – a new row will be added to product_shop table, with $product_id and $shop_id values.
Likewise, we can detach a relationship – let’s say, we want to remove a product from the shop:
$shop->products()->detach($product_id);
Or, more brutally, remove all products from a particular shop – then just call method without parameters:
$shop->products()->detach();
You can also attach and detach rows, passing array of values as parameters:
$shop->products()->attach([123, 456, 789]); $shop->products()->detach([321, 654, 987]);
And another REALLY useful function, in my experience, is updating the whole pivot table. Really often example – in your admin area there are checkboxes for shops for a particular product, and on Update operation you actually have to check all shops, delete those which are not in new checkbox array, and then add/update existing ones. Pain in the neck.
Not anymore – there’s a method called sync() which accept new values as parameters array, and then takes care of all that “dirty work” of syncing:
$product->shops()->sync([1, 2, 3]);
Result – no matter what values were in product_shop table before, after this call there will be only three rows with shop_id equals 1, 2, or 3.
Additional Columns in Pivot Tables
As I mentioned above, it’s pretty likely that you would want more fields in that pivot tables. In our example it would make sense to save the amount of products, price in that particular shop and timestamps. We can add the fields through migration files, as usual, but for proper usage in relationships we have to make some additional changes to Models:
public function products() { return $this->belongsToMany('App\Product') ->withPivot('products_amount', 'price') ->withTimestamps(); }
As you can see, we can add timestamps with a simple method withTimestamps and additional fields are added just as parameters in method withPivot.
Now, what it gives us is possibility to get those values in our loops in the code. With a property called pivot:
foreach ($shop->products as $product) { echo $product->pivot->price; }
Basically, ->pivot represents that intermediate pivot table, and with this we can access any of our described fields, like created_at, for example.
Now, how to add those values when calling attach()? The method accept another parameter as array, so you can specify all additional fields there:
$shop->products()->attach(1, ['products_amount' => 100, 'price' => 49.99]);
Conclusion
So, pivot tables and many-to-many relationships are handled quite conveniently with Eloquent, so there’s no need to create a separate model for intermediate table. Hope that helps!
Want to learn more?
Watch my free video called Advanced Pivot Tables in Many-to-Many.
It’s one preview lesson from my online-course Eloquent: Expert Level.
[…] Laravel Daily has a new post on pivot tables and many-to-many relationships: […]
Hi, want to thank you for such a great article on the topic.
Also, it would be nice to explain how to do aggregates of the pivot data, like to sum the quantity of products sold on many shops or the count for those products.
Thanks
Thanks for kind words Uziel, ok adding to my to-do list some deeper pivot tables situations. Although with more complicated aggregates there are often Raw queries involved.
sure man. i will explain tomorrow how to do aggregates of the pivot data
thank you very much!!!
np
Another Laravel mis-naming. Those aren’t “pivot tables”, they’re “association” tables.
http://paul-m-jones.com/archives/6068
True that — not sure why the laravel community can’t call something by the same name that the rest of the IT world has been using for decades.
Or junction table, am I right?
Totally, the learning becomes somewhat difficult for that.
Very nice article! thanks.
It’d be nice to explain how to get and update specific fields in an specific product_shop row.
For example, I want to update the price in a shop whom ID is 4.
Shop::find($shopId)->products()->updateExistingPivot($productId, $attributesArray);
How about getting the price?
Very Nice article. Just when I needed it the most. I second Uziel said and looking forward to it.
Loving Laravel Daily.
Np
Thanks. I got clear idea about Many To Many relationship after reading this blog
.
What if my extra fields in a pivot table is a foreign key? How can I get the row of that foreign key from the the other table?Thanks
Looking for this answer as well
Ok, this is awesome. I’ve learned a lot here… but I have a question…
So, say I have multiple stores, each with the same product, but each store has a different price.
How would I query store 1 for product 1 only – and use the pricing from the pivot table?
I can get a list of all the stores. I can list all the products for a single store… but I need to show a single product from a specific store.
He showed it when he mentioned the pivot extra fields. Amount of products and price part. Exactly what you’re asking
Not really, it just shows how to add extra data to the pivot table, not how to fetch specific row from it.
@John: The relationship returns a Collection, so you should be able to use the method ->get(). i.e.: to get the price from a desired product in a shop you could do:
$shop->products->get($product_id)->pivot->price;
[…] Pivot Tables and Many-to-Many Relationships by Povilas Korop […]
I am a beginner in the laravel 5 framework. I am trying to work with eloquent model relationships and not able to get the desired output.
I have created 3 tables:
user(id,name)
company(c_id, company_name)
usercompany (id,c_id) this is the PIVOT TABLE (many to many)
I want to retrieve all the data from user and company table using the pivot table: usercompany
THE MyUser Model:
class MyUser extends Model
{
protected $table = ‘user’;
public function getCompany()
{
return $this->belongsToMany(‘App\MyCompany’, ‘usercompany’, ‘id’, ‘c_id’);
}
}
THE MyCompany Model:
class MyCompany extends Model
{
protected $table = ‘company’;
public function getUser()
{
return $this->belongsToMany(‘App\MyUser’, ‘usercompany’, ‘id’, ‘c_id’);
}
}
The Controller:
class MyUserCompanyController extends Controller
{
public function companys()
{
$companys = MyUser::all();
return View::make(‘companys’)->with(‘companys’,$companys);
}
}
The View:
@foreach ($companys as $company)
{{ $company->getCompany->company_name}}
@endforeach
The Route:
Route::get(‘/companys’,’MyUserCompanyController@companys’);
I am getting the following Error:
ErrorException in Connection.php line 651: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘company.id’ in ‘on clause’ (SQL: select `company`.*, `usercompany`.`id` as `pivot_id`, `usercompany`.`c_id` as `pivot_c_id` from `company` inner join `usercompany` on `company`.`id` = `usercompany`.`c_id` where `usercompany`.`id` = 1) (View: C:\xampp\htdocs\fundhound_final\resources\views\companys.blade.php)
Thanks in advanced.
hi
thanks for your lovely article.
i’m beginner in the laravel and i want to implements a many to many relationship with 3 or more entity in laravel but i don’t know how to do it.
i’m very glad to help me.
thanks again.
Thanks!!!! You’re an excellent teacher, I was even clearer operation relations. I have also written in my blog about laravel Eloquent, Laravel query builder, Laravel eloquent Relationship. Here is very easy explanation of Eloquent vs Query builder http://growthefuturenow.com/laravels-eloquent-orm-versus-query-builder/
It is a great article..Thanks for such a great explanation.This helps me to move ahead with my project.
Thank you so much. The eloquent documentation is not clear about this and I believe there’re errors in there too. I’m new to laravel and in the docs, that part seem disjointed, but here it makes sense. It’s not even clear on “alphabetical” blah blah, and not clear on the goal of the example. There’re no table examples, like you did and the author assumed that the reader already understands the concepts; pivot tables. I was reading it just now and thought ahhhhhh this is now confusing me let me find out more from someone else and here I’m… Brilliant!
Whoever wrote that documentation part needs to revise it or as I noticed, documentation has git repo, which I think needs updating to clear this. Please correct it for others and ourselves so that we have a clearer documentation of this to refer to.
Wow…Super simple
Thank you so much for posting a great article. I am getting some errors while updating the pivot table content. Will you please solve this. Thanking you.
Is Products a typo? Did you mean Product?
I have a hasMany relationship as below.
A Projectcampaign has many breakupcost and I have a page from where I want to add breakupcost. I have +Add more button in that, A user can add his desired rows of breakupcost.
I have same form for add and edit, Is there any sync method for doing the same, if the breakupcost exist and it will not insert that again and autoremove a breakupcost If user deleted it by -Delete button.
Please help in the same
How we can send the result to view ????
i have 2 tables channels and roups (channel can be in many groups and group can have many channels)
public function detailGroup($id) {
$chanelsName = array();
$chanelsName[] = Group::find($id)->channels->each(function($channel) {
return $channel->name;
});
var_dump($chanelsName[0][0]->name);
//$group = compact(‘group’);
//echo $chanelsName[0];
//return view(‘admin.groups.group’, compact(‘group’), $chanelsName );
}
If you arrive here – as I did a week ago – not fully understanding when you would need to use sync() – BOOKMARK THIS PAGE and read it every day! Managing dynamic updates to many-to-many relationships can be extremely hard to get your head around – at least it was for me. This article gave me the ‘eureka’ moment.
How you can add one product in multiple shops at a time? Should we loop through each shop and add the product or is there any other way of using that in single query like we are attaching multiple products in single shop.
I used like this.
$product->shops()->attach([3,4,5,]);
Is it require any modifications?
Thank you very much for the article Povilas!
I am new to Laravel and spent about a week trying to filter a collection by additional fields in the pivot table, before I came across your blog.
Very nice article…thanks for sharing Povilas.
I’m a newbie in laravel and currently working on authentication part of one of my projects.
I’m using laravel 5.
I’ve multiple users like employees, clients, admin and super-admin. Employees and Client have different info, but admin and super-admin share same info. So I ‘m trying to use polymorphic Many to Many relationship by using different tables/models for Users(admin & super-admin combined into one table), Employees, Clients, Role and Roleable table.
I just wanted to know whether above structure is correct or not ,if in future, more users-types/roles are added to the system.
Also wanted to know one more thing: What is the difference between Many-To-Many and Polymorphic Many-To-Many relationships. I read many blogs, went through lots of articles and codes, but couldn’t understand this difference.
It will be really great if you can clear my doubts.
Thanks,
Dipti
In Model, how do you calculate those amount and price from pivot table? So you have it with one query.
Thanks for this in-depth article, it was very helpful!
Thank you so much.
Excellent! withPivot solved a major headache for me. 🙂
Very good article as always !!
I am new to Laravel. I do a lot search on relationship but still a point is not clear to me. Mostly all the examples of relationships are on single object like $shop = Shop::find($shop_id);; What is we have a object that returns multiple rows like $shops = Shop::where(‘status’, ‘=’ ,1); should we still use relationship inside the $shops loop like following:
foreach($shops as $shop){
$products = $shop->products();
}
OR we should use Joins on query builder in this situation. What is the best way?
As per my understanding if we call relationship inside the loop then it’s the same like we are executing a database query inside a loop which is wrong way.
foreach($shops as $shop){
$products=Product::where(‘shop_id’,’=’,$shop->id)
}
Please guide me and let me know if we should joins in this situation and relationships are only good if we have a single row object.
public function products()
{
return $this->belongsToMany(‘App\Products’, ‘products_shops’);
}
seems like you meant shops() in the method name or ‘App\Shops’ in the relationship class.
Hi everyone,
What about timestamps() in the pivot table ? By default, i believe that they’re not set when you do save().
How can we have the created_at and updated_at all set when saving in the pivot table ?
Hello Povilas …
I’m beginner in Laravel and this article is very useful for me… very thanks to spend time and prepare this for us..
It’s Excellent…
Hi there can you please take a look at this problem I’m facing? For example I want to be able to select all the checkboxes of products that belongs to a shop when I try to edit a shop record, and then do the syncing method you talk about. I’v posted a thread on laracasts.com and stackoverflow but haven’t gotten any real response. Can you please take a look? https://laracasts.com/discuss/channels/general-discussion/display-values-that-are-checked-when-editing-in-laravel https://stackoverflow.com/questions/44941971/display-values-that-are-checked-when-editing-in-laravel
Hi, this is the best article and a very simple to understands. Thanks a lot for take your time and explain it to IT community.
I have a question: How can I add a new SHOP and related it to products?
I have:
$shop = Shop::find(1);
$product = new Product([‘name’ => ‘xxx’]);
$shop->products()->save($product);
Thanks a lot! Had troubles to understand it from the Laravel docs, but your example made my day!
very helpful article
Very well structured article. Thanks.
How do you list all shops using query builder that has only this products? [‘tv’, ‘radio’, ‘dvd_player’, ‘chromcast’]
Now I got it!
Hi, I have a peculiar situation where I have ‘courses’ and ‘students’ tables with a pivot of ‘course_student’. In the pivot table I was able to add additional columns ‘enroll_date’ and ‘grade’ successfully.
Now I want to COUNT(*) students and groupBy(YEAR(enroll_date)) for a single course. I don’t know how else to go about it.
I’d appreciate any help.
In Shop model you’re calling App\Products, but you don’t have App\Products right? you have App\Product, is that valid?
Correct, it should be App\Product, will change in the article. Thank you for the notice.
Thank you, you helped me a lot.
Wow, thank you so much. I was using justly the model to pivot table and I wasn’t achieving success when I was realizing the query. Now works fine. Thanks =)
Thanks You for this tutorial.
Thank you . it is very helpful tutorial for laravel bigners
thanks for making this article. it’s really helpful for me to use many to many relations with in 5 minute .
Thanks Again for this article with a real and simple example.
Hi,
I have tables.
contacts
id
fname
lname
groups
id
group_name
contacts_groups.
id
contacts_id
groups_id
groups model.
public function contacts()
{
return $this->belongsToMany(‘App\contacts’);
}
contacts model.
public function groups()
{
return $this->belongsToMany(‘App\groups’);
}
in blade file.
@foreach($contact->groups as $p)
{{$p->group_name}}
@endforeach
What not getting the name just getting groups_id got printed here form pivot table. What I am doing wrong in here. This is my very first programme in Laravel want to lear it.
Best Explanation Ever on Laravel Relationships. You just covered the most Confusing Topic in a really very easy way. Thankyou very much for this post. I appreciate it a lot.
nice article how do i get data that is not in pivot table but present in one of the tables say product
I realize this is a very old article but I just spent a good part of the day pulling my hair out trying to figure out a bug in a many-to-many relationship on a project I’m working on. Reading this post partially helped me understand what the problem was and I managed to fix it.
Thank you for writing this! I’m bookmarking this post for future reference 🙂
Thank you for this article. It helped me a lot.
Wow.. I’ve researched about that so much and I’ve never found a good example/tutorial. (shame on me) Now I understand to handle the pivot tables on the laravel way. Thanks mate!
/cheers from https://spawnkey.de
Thanks a lot!
“Name of the pivot table should consist of singular names of both tables, separated by undescore symbole and these names should be arranged in alphabetical order, so we have to have product_shop, not shop_product.”
“should be arranged in alphabetical order” ? Where did you get that?
Official Laravel docs: https://laravel.com/docs/master/eloquent-relationships#many-to-many
“The role_user table is derived from the *alphabetical* order of the related model names, and contains the user_id and role_id columns.”
[…] think this link could help you, was handy for me when I needed to learn pivoting: http://laraveldaily.com/pivot-tables-and-many-to-many-relationships/ Also, in your model to actually get the current Authed user ID, you can use Eloquent Model Events: […]
[…] Add your migrations, NB this app has a pivot table https://laraveldaily.com/pivot-tables-and-many-to-many-relationships/ […]
get all products except which are used in pivot table
Very helpful. Thanks a lot
Very helpful and simple implementation. Bravo
Hello sir there is a very nice video on multiple select you have done. However am trying to come up with such a multiple select and it really fascinating kindly assist