Welcome to the incredibly popular Easy Laravel 5 companion blog. To celebrate the new edition's release (updated for Laravel 5.5!) use the discount code easteregg to receive 20% off the book or book/video package! » Buy the book

Did you know this material has been completely updated for Laravel 5.5 in the latest edition of Easy Laravel 5? Check out the new companion project.

You'll use the many-to-many relation when the need arises to relate a record in one table to one or several records in another table, and vice versa. Consider some future version of TODOParrot that allowed users to classify lists using one or more categories, such as "leisure", "exercise", "work", "vacation", and "cooking". A list titled "San Juan Vacation" might be associated with several categories such as "leisure" and "vacation", and the "leisure" category would likely be associated with more than one list, meaning a list can be associated with many categories, and a category can be associated with many lists. In this detailed post you'll learn all about the many-to-many relation.

See the below diagram for an illustrative example of this relation.

In this post (adapted from my bestselling book, Easy Laravel 5) you'll learn how to create the intermediary table used to manage the relation (known as a pivot table), define the relation within the respective models, and manage the relation data.

Creating the Pivot Table

Many-to-many relations require an intermediary table to manage the relation. The simplest implementation of the intermediary table, known as a pivot table, would consist of just two columns for storing the foreign keys pointing to each related pair of records. The pivot table name should be formed by concatenating the two related model names together with an underscore separating the names. Further, the names should appear in alphabetical order. Therefore if we were creating a many-to-many relationship between the Todolist and Category models, the pivot table name would be category_todolist. Of course, the Category model and corresponding categories table also needs to exist, so let's begin by generating the model:

$ php artisan make:model Category --migration

You'll find the newly generated model inside app/Category.php:

use Illuminate\Database\Eloquent\Model;

class Category extends Model {

}

Next, modify the newly created migration file's up() method to look like this:

public function up()
{
  Schema::create('categories', function(Blueprint $table)
  {
    $table->increments('id');
    $table->string('name');
    $table->timestamps();
  });
}

Finally, run Artisan's migrate command to create the table:

$ php artisan migrate

With the Category model and corresponding categories table created, let's next create the category_todolist table:

$ php artisan make:migration create_category_todolist_table \
  --create=category_todolist
Created Migration: 2016_04_05_011409_create_category_todolist_table

Next, open up the newly created migration (database/migrations/) and modify the up method to look like this:

public function up()
{
  Schema::create('category_todolist', function(Blueprint $table)
  {
      $table->integer('category_id')->unsigned()->nullable();
      $table->foreign('category_id')->references('id')
            ->on('categories')->onDelete('cascade');

      $table->integer('todolist_id')->unsigned()->nullable();
      $table->foreign('todolist_id')->references('id')
            ->on('todolists')->onDelete('cascade');

      $table->timestamps();
  });
}

This syntax is no different than that used for the earlier tasks table migration. The only real difference is that we're referencing two foreign keys rather than one.

After saving the changes run Artisan's migrate command to create the table:

$ php artisan migrate

Defining the Many-to-Many Relation

With the tables in place it's time to define the many-to-many relation within the respective models. Open the Todolist model and add the following method to the class:

public function categories()
{
    return $this->belongsToMany('App\Category')
      ->withTimestamps();
}

Notice I've chained the withTimestamps method to the return statement. This instructs Laravel to additionally update the category_todolist timestamps when saving a new record. If you choose to omit the created_at and updated_at timestamps from this pivot table (done by removing the call to $table->timestamps from the migration), you can omit the withTimestamps method).

Save the changes and then open the Category model, adding the following method to the class:

public function todolists()
{
    return $this->belongsToMany('App\Todolist')
      ->withTimestamps();
}

After saving these changes you're ready to begin using the relation!

Associating Records Using the Many-to-Many Relation

You can associate records using the many-to-many relation in the same way as was demonstrated for one-to-many relations; just traverse the relation and use the save method, as demonstrated here:

$tl = Todolist::find(1);

$category = new Category(['name' => 'Vacation']);

$tl->categories()->save($category);

In order for this particular example to work you'll need to make sure name has been added to the Categorymodel's fillable property.

After executing this code you'll see the new category has been created and the association between this newly created category and the list has been made:

mysql> select * from categories;
+----+----------+---------------------+---------------------+
| id | name     | created_at          | updated_at          |
+----+----------+---------------------+---------------------+
|  1 | Vacation | 2016-04-04 20:44:11 | 2016-04-04 20:44:11 |
+----+----------+---------------------+---------------------+

mysql> select * from category_todolist;
+-------------+-------------+------------+------------+
| category_id | todolist_id | created_at | updated_at |
+-------------+-------------+------------+------------+
|           1 |           1 | ...        | ...        |
+-------------+-------------+------------+------------+

The above example involves the creation of a new category. You can easily associate an existing category with a list using similar syntax:

$list = Todolist::find(2);

$category = Category::find(1);

$list->categories()->save($category);

You can alternatively use the attach and detach methods to associate and disassociate related records. For instance to both associate and immediately persist a new relationship between a list and category, you can either pass in the Category object or its primary key into attach. Both variations are demonstrated here:

$list = Todolist::find(2);

$category = Category::find(1)

// In this example we're passing in a Category object
$list->categories()->attach($category);

// The number 5 is the primary key of another category
$list->categories()->attach(5);

You can also pass an array of IDs into attach:

$list->categories()->attach([3,4]);

To disassociate a category from a list, you can use detach, passing along either the Category object, an object's primary key, or an array of primary keys:

// Pass the Category object into the detach method
$list->categories()->detach(Category::find(3));

// Pass a category's ID
$list->categories()->detach(3);

// Pass along an array of category IDs
$list->categories()->detach([3,4]);

Determining if a Relation Already Exists

Laravel will not prevent you from duplicating an association, meaning the following code will result in a list being associated with the same category twice:

$list = Todolist::find(2);

$category = Category::find(1)

$list->categories()->save($category);
$list->categories()->save($category);

If you have a look at the database you'll see that the Todolist record associated with the primary key 2 has been twice related to the Category record associated with the primary key 1, which is surely not the desired behavior:

mysql> select * from category_todolist;
+-------------+-------------+------------+------------+
| category_id | todolist_id | created_at | updated_at |
+-------------+-------------+------------+------------+
|           1 |           2 | ...        | ...        |
|           1 |           2 | ...        | ...        | 
+-------------+-------------+------------+------------+

You can avoid this by first determining whether the relation already exists using the contains method:

$list = Todolist::find(2);

$category = Category::find(1)

if ($list->categories->contains($category))
{

  return Redirect::route('lists.show', [$list->id])
    ->with('message', 'Category could not be assigned. Duplicate entry!');

} else {

  $list->categories()->save($category);

  return Redirect::route('lists.show', [$list->id])
    ->with('message', 'The category has been assigned!');

}

Saving Multiple Relations Simultaneously

You can use the saveMany method to save multiple relations at the same time:

$list = Todolist::find(1);

$categories = [
  new Category(['name' => 'Vacation']),
  new Category(['name' => 'Tropical']),
  new Category(['name' => 'Leisure']),
];

$list->categories()->saveMany($categories);

Traversing the Many-to-Many Relation

You'll traverse a many-to-many relation in the same fashion as described for the one-to-many relation; just iterate over the collection:

$list = Todolist::find(2);

...

@if ($list->categories->count() > 0)

  <ul>

  @foreach($list->categories as $category)

    <li>{{ $category->name }}</li>

  @endforeach

  </ul>

@endif

Because the relation is defined on each side, you're not limited to traversing a list's categories! You can also traverse a category's lists:

$category = Category::find(2);

...

@if ($category->todolists()->count() > 0)

  <ul>

  @foreach($category->todolists as $list)

    <li>{{ $list->name }}</li>

  @endforeach

  </ul>

@endif

Synchronizing Many-to-Many Relations

Suppose you provide users with a multiple selection box that allows users to easily associate a list with one or more categories. Because the user can both select and deselect categories, you must take care to ensure that not only are the selected categories associated with the list, but also that any deselected categories are disassociated with the list. This task is a tad more daunting than it may at first seem. Fortunately, Laravel offers a method named sync which you can use to synchronize an array of primary keys with those already found in the database. For instance, suppose categories associated with the IDs 7, 12, 52, and 77 were passed into the action where you'd like to synchronize the list and categories. You can pass the IDs into syncas an array like this:

$categories = [7, 12, 52, 77];

$tl = Todolist::find(2);

$tl->categories()->sync($categories);

Once executed, the Todolist record identified by the primary key 2 will be associated only with the categories identified by the primary keys 7, 12, 52, and 77, even if prior to execution the Todolist record was additionally associated with other categories.

Managing Additional Many-to-Many Attributes

Thus far the many-to-many examples presented in this chapter have been concerned with a join table consisting of two foreign keys and optionally the created_at and updated_at timestamps. But what if you wanted to manage additional attributes within this table, such as some additional description pertaining to the list/category relation?

Believe it or not adding other attributes is as simple as including them in the table schema. For instance let's create a migration that adds a column named description to the category_todolist table created earlier in this section:

$ php artisan make:migration add_description_to_category_todolist_table
Created Migration: 2016_04_05_012822_add_description_to_category_todolist_table

Next, open up the newly generated migration file and modify the up() and down() methods to look like this:

public function up()
{
  Schema::table('category_todolist', function($table)
  {
      $table->string('description');
  });
}

public function down()
{
  Schema::table('category_todolist', function($table)
  {
      $table->dropColumn('description');
  });
}

Save the changes and After generating the migration be sure to migrate the change into the database:

$ php artisan migrate
Created Migration: 2016_04_05_012822_add_descript...

Finally, you'll need to modify the Todolist categories relation to identify the additional pivot column using the withPivot() method:

  public function categories()
  {
      return $this->belongsToMany('App\Category')
        ->withPivot('description')
        ->withTimestamps();
  }

With the additional column and relationship tweak in place all you'll need to do is adjust the syntax used to relate categories with the list. You'll pass along the category's ID along with the description key and desired value, as demonstrated here:

$list = Todolist::find(2);
$list->categories()->attach(
  [3 => ['description' => 'Because San Juan is a tropical island']]
);

If you later wished to update an attribute associated with an existing record, you can use the updateExistingPivot method, passing along the category's foreign key along with an array containing the attribute you'd like to update along with its new value:

$list->categories()->updateExistingPivot(3, 
  ['description' => 'Sun, beaches and rum!']
);

Conclusion

I congratulate you for making it to the end of this epic post! Chapter 4 of my bestselling book Easy Laravel 5goes into great detail about all of Laravel's supported relations (one-to-one, belongs to, etc.). If you'd like to purchase a copy, use the discount code easteregg when checking out for a 20% discount!

来自  https://www.easylaravelbook.com/blog/introducing-laravel-many-to-many-relations/

[Proposal] Using Pivot table values in Belongs To Many Where queries.

+----+------------+---------+-------+
| id | company_id | user_id | owner |
+----+------------+---------+-------+
|  1 |          1 |       1 |     1 |
|  2 |          1 |       2 |     0 |
|  3 |          1 |       3 |     0 |
+----+------------+---------+-------+

$owners = array();
foreach (Company::find(1)->users as $user) {
    if ($user->pivot->owner) {
        $owners[] = $user;
    }
}
return $owners;

return Company::find(1)->users()->wherePivot('owner', 1)->get();

public function wherePivot($column, $operator = null, $value = null, $boolean = 'and')
{
    $column = $this->table.'.'.$column;
    return $this->where($column, $operator, $value, $boolean);
}

public function orWherePivot($column, $operator = null, $value = null)
{
    $column = $this->table.'.'.$column;
    return $this->orWhere($column, $operator, $value);
}

Member

taylorotwell commented on 2 Nov 2013

Can't you already just do: Company::find(1)->users()->where('company_user.owner', 1)->get();

 3

Contributor

valorin commented on 2 Nov 2013

Yes, but that method isn't documented anywhere. So you've gotta either know it works, or know how the where() functions operate to have an idea that it will work... or I'm just really slow :-) I thought about doing it that way, but given that Eloquent automatically links class names to tables, including pivot tables, it seemed inconsistent to have to manually specify the pivot table name in this one instance. It makes much more sense, from my point of view, to have Eloquent manage the table name behind the scenes, like everything else. Another factor is that you might not want to hard code your pivot table name in multiple places, in case it needs to be changed in the future. Anyway, if you don't agree that it's needed then I'll see if I can update the documentation with a line explaining the workaround instead.

Contributor

anlutro commented on 2 Nov 2013

There is the argument that if you update the pivot table name, you have to update the where statement as well. A wherePivot function might be a nice abstraction even though the functionality is already in place.

Member

taylorotwell commented on 26 Nov 2013

Done.

 2

Contributor

garygreen commented on 21 Feb 2014

This is very useful though when using this in combination with sync it would seem logical to use this wherePivot to pick out/filter what rows should be synced exactly. At the moment when syncing the where seems to be completely ignored.

budhajeewa commented on 19 Mar 2014

I agree with @garygreen . I have a Team model that has a relationship called admins, like this: public function admins() {    return $this->belongsToMany( 'User' )->wherePivot( 'userrole_id', '=', 1 ); } This is the team_user table I have: id team_id user_id userrole_id ... ... ... ... 16 9 4 1 17 9 4 2 18 9 5 1 19 9 5 2 ... ... ... ... When I fetch admins of a team like $team->admins, the User with id 4 is returned fine. But when I do $team->admins()->detach(4), not only the row with id 16 is removed, but also the 17th row. In other words, all records with user_id 4 are removed, regardless of the wherePivot() method. I would like to get a table like following: id team_id user_id userrole_id ... ... ... ... 17 9 4 2 18 9 5 1 19 9 5 2 ... ... ... ... But I get this: id team_id user_id userrole_id ... ... ... ... 18 9 5 1 19 9 5 2 ... ... ... ... It's great if this gets fixed.  I am currently using this workaround: public function detachAdminById( $adminId ) {    return DB::table( 'team_user' )            ->where( 'user_id', '=', $adminId, 'and' )            ->where( 'userrole_id', '=', 1 )            ->delete();    /* TODO:      Replace above code with the below, when Laravel supports it.      Below code currently detaches all records in 'team_user' table, where      'user_id' = $adminId.          return $this->admins()->detach($adminId);     */ }

 1

manthan787 commented on 10 Oct 2014

I'm also facing the same issue as @budhajeewa It would be nice to be able to access the ID of the pivot table, so they can be deleted selectively.

budhajeewa commented on 10 Oct 2014

@manthan787

Shemahmforash commented on 28 Oct 2014

I'm also in the same situation as both @budhajeewa and @manthan787. Currently I am using using a workaround similar to the above one, but I think it would be nice if laravel allowed us to access the pivot table primary key, or, even better, be able to detach a many to many relationship according to a certain condition in the extra items of the pivot table.

InfiniteDevelopment commented on 8 Nov 2014

This is exactly the issue I am having..... I have a table for users one for relationships and I am pulling back all of the user objects where the relationship exists but no where can I parse for only the accepted connections which live in the relationships table.... I think wherePivot would be awesome.

InfiniteDevelopment commented on 8 Nov 2014

I added: public function wherePivot($column, $operator = null, $value = null, $boolean = 'and') { $column = $this->table.'.'.$column; return $this->where($column, $operator, $value, $boolean); } public function orWherePivot($column, $operator = null, $value = null) { $column = $this->table.'.'.$column; return $this->orWhere($column, $operator, $value); } To the laravel install and did: //count the connections $recievedConnectionsCount = $user->aUsersRecievedConnections()->wherePivot('state', '=', 'accepted')->count(); $sentConnectionsCount = $user->aUsersSentConnections()->wherePivot('state', '=', 'accepted')->count(); and it gave me the intended result. Maybe someone should do a pull request and get this integrated into the base of laravel so I dont lose it when updating the framework... :)

bravist commented on 12 Jan 2016

return Company::find(1)->users()->wherePivot('owner', 1)->get(); perferect

athamid commented on 12 Jan 2017

how can I Use andWherePivot? I need check two where on pivot table. @taylorotwell

 1

ccastillejo-accent commented on 29 Mar • 

edited 

I'm also in the same situation and follow @budhajeewa response I make this function: use Illuminate\Support\Facades\DB; use Illuminate\Database\Capsule\Manager as DBManager; /**     * @param string $relation     * @param integer $relatedId     * @param array $updateData     * @param callable|null $builderExtend     * @return bool|int     */    public function updatePivot($relation, $relatedId, $updateData, $builderExtend = null)    {        if (!method_exists($this, $relation)) {            return false;        }        $relation = $this->$relation();        try {            $dbBuilder = DB::table($relation->getTable());        } catch (\RuntimeException $e) {            $dbBuilder = DBManager::table($relation->getTable());        }        $dbBuilder->where($relation->getForeignPivotKeyName(), $this->id)            ->where($relation->getRelatedPivotKeyName(), $relatedId);        if (is_callable($builderExtend)) {            $builderExtend($dbBuilder);        }        return $dbBuilder->update($updateData);    } Put this in some class that extends Model or in trait file

来自 https://github.com/laravel/framework/issues/2619