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');