Working with Pivot Tables in Many to Many Relationships in Laravel

consider a Many-to-Many relationship:

a book can have many authors and every author can have many books.

so the table structure is something like this.

books:

• id

• title

authors:

• id
• name

book_author:

• book_id
• author_id

and the models:

class Book extends Model
{   
  public function authors()      
   {             
    return $this->belongToMany(Author::class);       
   }
}

class Author extends Model{  
 public function books()     
 {             
   return $this->belongToMany(Book::class);       
 }

}

the book_author table is called a pivot table which stores the ids of authors and books related to each other.

we know how to get the related data of each model:

$authors = $book->authors();
$books = $author->books();

but if you want to get the intermediate table? for that, there is an attribute called pivot you can use:

after getting the relationship for each record we can use pivot:

$author = Author::where('name','Seth Godin')->first(); 

foreach($author->books as $book)
{
   echo  $book->pivot->created_at;
}

by default, only book_id and author_id will be presented by pivot model.

if you have other attributes you want it to include you can specify them when you define the relationship.

return $this->belongsToMany(Author::class)->withPivot('is_main_author');

changing the “pivot” name

you can change the pivot attribute name. For example, we might want to call a main_author method instead of pivot. we can define that when defining the relationship:

return $this->belongsToMany(Author::class)->as('main_author')->withTimesTamps();

so instead of using pivot we can use main_author.

$author = Author::where('name','Seth Godin')->first(); 

foreach($author->books as $book)
{
   echo  $book->main_author->updated_at;
}

Order Queries Based on the pivot table

we can use orderByPivot method when you define the belongsToMany relationship to order queries.

return $this->belongsToMany(Author::class)->where('is_main_author',1)->orderByPivot('created_at');