All you need to know about Pivot Tables

Guide-to-laravel-Pivot-TAble

In this post, I am going to talk about many-to-many relationships and Pivot Tables. You heard about it, perhaps used it but you still don’t fully understand the concept, then keep on ready this post.  Pivot tables are very helpful when designing  database structure but can be difficult to understand sometimes. Pivot tables are intermediate tables that facilitates relationships between two main tables and even within one table.

When exactly you need Pivot Table

First of all, let’s clear our understanding of scenarios where Pivot Table can help us.  As I said  it provides many-to-many relationship between two tables, let’s look at some examples when exactly one need many-to-many-relationship and Pivot Table.

Categories-Posts Example

If you have used WordPress, you know that you can assign multiple categories to a post. So here, a post can reside in multiple categories and a category can have multiple posts in it. Technically

Post -> Belongs To Many -> Categories

Categories -> Belongs To Many -> Posts

that’s a very basic scenario where you will need pivot table. Here’s what the table structure will look like.

posts{id, title, content}

categories{id, title}

category_post(id, category_id, post_id}

This last table (categories_posts) is Pivot Table . Few Points to clear here:

  1. By convention, the pivot table is named as the singular form of both tables separated by underscore and tables should be arranged alphabetically. It’s just convention, not a rule. You can name it whatever you want. But then you have to tell Laravel what you named it, that’s it.
  2. Fields – At a minimum,  you are required to have two fields in pivot table i.e. foreign keys to both the tables, one to posts and one to categories. But you can add additional fields as required, Laravel doesn’t place any restriction on additional fields.
  3. If you use this  extension  then you don’t need to create the migration for this table manually. you can use  php artisan make:migration:pivot tags posts

Few Other examples will be:

User-Roles

Where a User can have any number of roles and a Role can have many users in it. Pivot table will be named role_user with fields id, role_id, user_id

Projects-Member

Where a member can be part of many projects at a time and Similarly a project can have many members assigned to it.

I hope these examples clear the concept and you know when to use Pivot tables.

How to create many-to-many relationships in Model

Once you have your tables ready it’s pretty easy to define relationship in model

class Post extends Model{

  ...

  public function categories(){

    return this->belongsToMany('App\Category')
  }

  ...
}


class Category extends Model{

  ...

  public function Post(){

    return this->belongsToMany('App\Post')
  }

  ...
}

//this is how you'll use relationship.

$post = Post::find(1);

//this will return us all the comments of this post
//Note: we don't have to add brackets here. 
//Laravel automatically exposes a property of the 
//same name as the relationship function
$categories = $posts->categories


//To Find all posts of a category
$category = Category::find(1);
$posts = $category->posts;

Let’s look at a more complex and complete example

I recently worked on a project where we had two major entities. User and Store. General requirements were as follows:

  1. User could follow stores to receive updates from them.
  2. There are two type of follow.  A user can follow a store manually or it can be automatic, based on his interests.
  3. we needed to know the datetime when user starts following a store.
  4. Which users follows a store.

Migration for pivot table

class CreateTableFollows extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('follows', function (Blueprint $table) {
            $table->increments('id');
            $table->integer('follower_id');
            $table->integer('store_id');
            $table->enum('type', ['automatic', 'manual']);

            $table->foreign('follower_id')->references('id')
                                         ->on('users');
            $table->foreign('store_id')->references('id')
                                       ->on('stores');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('follows');
    }
}

if you are following closely, you’ll notice that we have broken nearly all conventions of pivot table here, whether it is table name or field name. We did it for readability.  You will see shortly what changes we had to do, when not following the conventions.

Store Model

class Store extends Model 
{ 
     public function followers()
     { 
            return $this->belongsToMany(\App\User::class, 'follows', 'store_id', 'follower_id')
            ->withTimestamps()
            ->withPivot('type')
    }
}

Several new things here. Our simple relationship declaration has turned a little complex. Let’s break it down

  1. belongsTOMany : we used a newer syntax this time. This function expects 4 arguments but when following conventions we need only one, which is the first one. The second argument is the name of the table Pivot Table, we need this because we didn’t follow the conventions. The third one is the name of the field which is the foreign key to this model and the last argument is the name of the foreign key field to other table of the relationships.
  2. withTimestamps: this function basically instructs Laravel to save the created_at and modified_at  fields automatically at the time of inserting records in Pivot Table. We had included the fields for the same in migration file.
  3. withPivot: you’ll see why we need this in a moment. For now, just remember, this basically tells Laravel that we’ll need to have access to  ‘type’ field as well along with main models in the relationship. Argument to this function is the name of the column and you can pass as many column names as you want.

Here’s the other part of relationship, User Model:

class User extends Model
{
 

 /**
 * all stores followed by standard users
 * @return QueryBuilder for Store model
 */
 public function followedStores(){
   return $this->belongsToMany(\App\Store::class, 'follows', 'follower_id', 'store_id')
   ->withTimestamps()
   ->withPivot('type')
 }
}

 

Now let’s see how we’ll perform some common operations on this structure.

When User starts following a store

$user->followedStores()->attach($storeId, ['type'=>'manual']);

you can pass array as the first argument as well if you want a $user to follow multiple stores at once. Alternatively you can use the following syntax when attachment multiple stores with pivot fields.

$user->followedStores()->attach([
                             $storeId1 => ['type'=>'manual'], 
                             $storeId2=>['type' => 'automatic']
                          ]);

When User Unfollows a starts following a store

$user->gt;followedStores()->detach($storeId); 

detach also accepts an array as the first argument.

Get a list of stores which user follows


$user = User::find(1);
$stores = User::followedStores;

how do we when User started following a store and whether it’s automatic follow or manual follow?


$user = User::find(1);
$stores = User::followedStores;

foreach($stores as $store){

//when use started following a store
$store->pivot->created_at

if($store->pivot->type == 'manual'){
echo "manually followed"
}
}

See how we used a property called Pivot? that’s what we used withPivot() for when declaring our relationships.

how about building to query to receive only stores which are followed manually by a user?


$user = User::find(1);
$manuallyFollowed = $this->stores()->wherePivot('type', '=', 'manual' );

wherePivot allows us to put conditions on the fields in pivot table. Remember though you need to add this fields when declaring your relationship, using method withPivot

Getting list of followers of a store


$store = $store::find(1);
$followers = $store->followers;

Rest of the logic remains same as described above. You can use Pivot property to access type of follower, created_at to know when users started following this store and apply conditions using wherePivot.

And Last useful function with pivot table is updating the pivot table. Most often your admin area there are checkboxes for selection when updating a record. For updating when updating a post table you might have checkboxes for each category. on Update operation, you need to check which of the already checked categories got removed, which new got added etc. Big Pain.

But this small little function makes it easy – 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->categories->sync([1,2,4]);

Conclusion

In Conclusion, Pivot tables are very handy and provide a very easy and convenient ways to achieve some pretty complex tasks and as a result, your code looks clean. You can further create a Model for pivot tables if required, but that’s totally upto you. Nothing’s there to stop you from treating a pivot table as a normal table with Model.

The following two tabs change content below.

Ankit Kumar

Team Leader at Logiciel Solutions
This is my personal blog. I post about Laravel, Angular, SQL and Web Technologies here. I have been into web development for 7 years and learning new things always interest me. Looking forward to find a teacher in you all.
  • Ajay Aggarwal

    Thanks for this useful post 🙂