What is a Pivot Table?
Generally, a Pivot Table is a statistics tool that summarises the information in a data set to get an ideal report.. In Laravel, Pivot Table is used to connect the relationship between two tables when the provided relationship is Many to Many.
A Pivot Table is used to:
- Summarise, sort, group, count, total or average data stored in the table.
- Allow advanced calculations and grouping by fields.
Pivot Table Example :
Let’s take an example of two tables named customers and sellers who store the details of the customer and seller respectively. By this, a customer can buy the products from various sellers likewise the sellers can sell their products to various customers.
Now we need to store information of sellers from who the customer has bought their products and also the information of customers to whom a seller sold their products. This is where the Pivot table has its uses since it comes under the Many to Many relationship conditions.
A Pivot table is an additional table that is used here to store the customer_id and the seller_id, linking the customer to the particular seller. Now each table row represents a customer which gives us the ability to have a connection between the customer and a seller through which they are linked.
Naming Standards to follow for Pivot Table:
- The naming of the Pivot Table must be in snake_cased model names in alphabetical order separated by an underscore.
- Example : Two tables named customers and sellers. Pivot table name will be customer_seller.
- The table name must be in singular and not any plural words.
- The relationship naming should be in plural since the defined relationship is Many-to-Many.
Steps to Create Pivot Table :
The following processes are for the example provided above.(customers and sellers)
- Create a migration for the pivot table.
- Run the migration. Command => php artisan migrate.
- Add the respective relationships in the models of the respective tables.
- In Customer Model
- In Seller Model
These are the steps to define a pivot table.
Attach and Detach :
The pivot table is created but we still need to store the data in the table. There are methods named attach() to insert the data in the pivot table and detach() method to remove it.
The seller is attached to the customer using the sellers relation defined in the customer model.
Conclusion :
These are the basic steps in implementing a pivot table in your project. There are much more advanced concepts on Pivot tables which in turn gives more effective way to manipulate data based on our requirements.
Surya Prakaash,PHP Team