Pivot tables and many-to-many relationships
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
shops
– id
– name
products
– id
– name
– id
– name
product_shop
– product_id
– shop_id
– 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:
app/Shop.php:
1
2
3
4
5
6
7
8
9
10
11
12
|
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:
app/Product.php:
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: