A case study of many-to-many relationships in Laravel

Data tables are cross-intersecting, interrelated, laravel one-to-many, a pair of more good understanding, this article focuses on the examples to explain the laravel in the many-to-many relationship, interested friends to see it together

Data tables are cross-intersecting, interrelated, laravel one-to-many, a pair of more good understanding, the official website is very detailed, I will not repeat it, focus on I write down many-to-many relationship

A common association is many-to-many, that is, a record of table A is associated with more than one record in table B through Intermediate table C, and vice versa. For example, a user has multiple roles, whereas a role corresponds to multiple users.

To test this correlation, we use an example of a user role on the official website:

You need three datasheets: the users, roles, and Role_user,role_user tables are named alphabetically by the associated model name (here Role_user is the intermediate table) and contain user_id and role_id two columns.

Many-to-many associations are defined by writing methods that return the results of the Belongstomany method. Nonsense does not say much, directly on the data structure:

1: Create a role table roles, and add some initialization data:

SET foreign_key_checks=0;--------------------------------Table structure for users------------------------------ DROP TABLE IF EXISTS ' users '; CREATE TABLE ' users ' (' id ' int () unsigned not null auto_increment, ' name ' varchar (255) COLLATE utf8_unicode_ci not NULL, ' Email ' varchar (255) COLLATE utf8_unicode_ci not NULL, ' password ' varchar ' COLLATE utf8_unicode_ci ' not null, ' Remember_ Token ' varchar ' COLLATE utf8_unicode_ci default null, ' CREATED_AT ' timestamp not null default ' 0000-00-00 00:00:00 ', ' u Pdated_at ' timestamp not NULL DEFAULT ' 0000-00-00 00:00:00 ', PRIMARY key (' id '), UNIQUE KEY ' users_email_unique ' (' email ') U SING BTREE) engine=innodb auto_increment=4 DEFAULT Charset=utf8 collate=utf8_unicode_ci;---------------------------- ----Records of users------------------------------INSERT into ' users ' VALUES (' 1 ', ' admin ', ' admin@163.com ', ' $2y$10$j/ Yxqscucanrhagzp9g6. TU1MD.SOLJX3M8WRHSUDRGAT4ZESUHC ', ' ilocxtjzjwhrmidlg1ckoyegecwqckuyx1pyaoluzy2ppscqft5ss7lbci7i ', ' 2016-04-21 16:26:23 ', ' 2016-12-14 09:29:59 '); INSERT into ' users ' VALUES (' 2 ', ' Baidu ', ' 10940370@qq.com ', ' $2y$10$ 2a5zj4pnj5ucp1dn3nx.5uj/ap7p6o4np2baa55afra8/rti1k6i2 ', null, ' 2016-04-22 06:48:10 ', ' 2016-04-22 06:48:10 '); INSERT Into ' users ' VALUES (' 3 ', ' Fantasy ', ' 1009@qq.com ', ', ' null, ' 2017-06-14 10:38:57 ', ' 2017-06-15 10:39:01 ');

2: Create a role table roles, and add some initialization data:

SET foreign_key_checks=0;--------------------------------Table structure for roles------------------------------ DROP TABLE IF EXISTS ' roles '; CREATE TABLE ' roles ' (' id ' int (ten) unsigned NOT NULL auto_increment, ' name ' varchar (255) COLLATE utf8_unicode_ci not NULL, ' Created_at ' timestamp not null default ' 0000-00-00 00:00:00 ', ' updated_at ' timestamp not null default ' 0000-00-00 00:00:00 ' , PRIMARY KEY (' id ')) engine=innodb auto_increment=7 DEFAULT Charset=utf8 collate=utf8_unicode_ci;-------------------- ------------Records of roles------------------------------INSERT into ' roles ' VALUES (' 1 ', ' Super Moderator ', ' 2016-04-21 16:26:23  ', ' 2016-12-14 09:29:59 '); insert into ' roles ' VALUES (' 2 ', ' Commander ', ' 2016-04-22 06:48:10 ', ' 2016-04-22 06:48:10 '); INSERT into ' Roles ' Values (' 3 ', ' Captains ', ' 2017-06-14 10:38:57 ', ' 2017-06-15 10:39:01 '); INSERT into ' roles ' values (' 4 ', ' director ', ' 2017-06- 10:41:41 ', ' 2017-06-15 10:41:51 '); INSERT into ' Roles ' VALUES (' 5 ', ' Regiment battle ', ' 2017-06-22 10:41:44 ', ' 2017-06-28 10:41:54 '); INSERTInto ' Roles ' VALUES (' 6 ', ' Creeps ', ' 2017-06-22 10:41:47 ', ' 2017-06-22 10:41:56 '); 

3: Create an intermediate table Role_user is used to record the corresponding relationship between the users table and the roles table, and to add some initialization data:

SET foreign_key_checks=0;--------------------------------Table structure for role_user---------------------------- --drop TABLE IF EXISTS ' Role_user '; CREATE TABLE ' Role_user ' (' id ' int (ten) unsigned NOT null auto_increment, ' user_id ' int (one) ' DEFAULT NULL ', ' role_id ' int (11 Default NULL, ' CREATED_AT ' datetime default NULL, ' UPDATED_AT ' datetime default NULL, PRIMARY KEY (' id ')) engine=myisam auto_increment=8 DEFAULT charset=latin1;--------------------------------Records of Role_user---------------------- --------insert INTO ' role_user ' VALUES (' 1 ', ' 1 ', ' 2 ', ' 2017-06-07 11:42:13 ', ' 2017-06-21 11:32:16 '); insert INTO ' role_use R ' Values (' 2 ', ' 1 ', ' 3 ', ' 2017-06-07 11:32:13 ', ' 2017-06-07 11:22:13 '); INSERT into ' role_user ' values (' 3 ', ' 2 ', ' 4 ', ' 20 17-06-07 11:32:13 ', ' 2017-06-07 11:12:13 '); INSERT into ' Role_user ' VALUES (' 4 ', ' 1 ', ' 5 ', ' 2017-06-07 11:32:13 ', ' 2017-06- 11:22:13 '); insert INTO ' Role_user ' VALUES (' 5 ', ' 3 ', ' 6 ', ' 2017-06-07 11:32:13 ', ' 2017-06-07 11:52:13 '); insert INTO ' rOle_user ' VALUES (' 6 ', ' 3 ', ' 2 ', ' 2017-06-07 11:32:13 ', ' 2017-06-07 11:42:13 '); INSERT into ' role_user ' values (' 7 ', ' 2 ', ' 2 ', ' 2017-06-07 11:42:13 ', ' 2017-06-07 11:52:13 ');

Note that when we define the intermediate table, we do not add s at the end and the naming rules are in alphabetical order, the role is placed in front, the user is placed behind, and is delimited by _, all in order to accommodate the default setting of the Eloquent Model association: When defining a many-to-many association, if no intermediate table is specified, Eloquent default intermediate tables are spliced with this rule.

Create a role model:

<?phpnamespace app\models;use illuminate\database\eloquent\model;/** * Class Role * @package app\models * @mixin \Eloq Uent */class Role extends model{}

Then we define the roles method on the User model:

<?phpnamespace app\models;use illuminate\database\eloquent\model;/** * Class User * @package app\models * @mixin \Eloq Uent */class User extends model{  /**   * Users role   *  /Public Function roles ()  {    return $this Belongstomany (' App\models\role ');}  }

Note: As we mentioned above, if the intermediate table is not role_user, then you need to pass the intermediate table as the second parameter into the Belongstomany method, if the fields in the intermediate table are not user_id and role_id, here we would like to name them $user_id and ROLE_ID, you need to pass $user_id as the third parameter into the method, $role _id passed in as the fourth parameter, if the associated method name is not roles, you can also pass the corresponding associated method name as the fifth parameter to the method.

Next we write the test code in the controller:

<?php$user = User::find (1); $roles = $user->roles;echo ' user '. $user->name. ' Owned role: '; foreach ($roles as $role)  echo $role->name. ' '; The corresponding output is: User admin has the role: Commander of the Army Regiment battle

Of course, as with all other relational types, you can call the roles method to add a conditional constraint to the associated query:

User::find (1)->roles ()->orderby (' name ')->get ();

As mentioned earlier, in order to determine the table name of the Association connection table, eloquent joins the names of the two associated models in alphabetical order. However, you can override this convention by passing the second argument to the Belongstomany method:

return $this->belongstomany (' app\models\role ', ' user_roles ');

In addition to the table name of the custom join table, you can also customize the column names of the fields in the table by passing extra parameters to the Belongstomany method. The third parameter is the foreign key name of the model you are defining the association relation to, and the fourth parameter you want to connect to the foreign key name of the models:

return $this->belongstomany (' app\models\role ', ' user_roles ', ' user_id ', ' role_id ');

To define relative relationships

To define an association relationship relative to many-to-many associations, simply call the Belongstomany method in the correlation model. We define the users method in the Role model:

<?phpnamespace app\models;use illuminate\database\eloquent\model;/** * Class Role * @package app\models * @mixin \Eloq Uent */class Role extends model{  /**   * Role User   *  /Public Function users ()  {    return $this Belongstomany (' App\models\user ');}  }

As you can see, the associated relationship of the definition is exactly the same as defined in its corresponding User, except that the former refers to App\models\role, which references App\models\user, because we re-use the Belongstomany method, All of the common table and key customization options are available when defining association relationships with many-to-many counterparts.

The test code is as follows:

$role = Role::find (2); $users = $role->users;echo ' role # '. $role->name. ' User: '; foreach ($users as $user) echo $user-& Gt;name. ' ';//The output is: Role # Commander The following users: admin fantasy Baidu

As you can see, dealing with many-to-many associations requires an intermediate table. Eloquent provides some useful ways to interact with this intermediate table, for example, we assume that the User object has a lot of Role objects associated with it, and after accessing these relationships, we can use the Pivot property on these models to access the intermediate table fields:

$roles = User::find (1)->roles;foreach ($roles as $role)   echo $role->pivot->role_id. ' <br> ';//corresponds to output: 2 3 5

Note that each Role model that we get is automatically assigned the pivot attribute. This property contains a model that represents an intermediate table and can be used like any other eloquent model.

By default, only the model primary key can be used on a pivot object, and if your pivot table contains additional properties, you must specify when you define the association relationship:

return $this->belongstomany (' App\models\role ')->withpivot (' Column1 ', ' column2 ');

For example, we modify the Role_user table by adding a field username data as follows:

To modify the model user:

<?phpnamespace app\models;use illuminate\database\eloquent\model;/** * Class User * @package app\models * @mixin \Eloq Uent */class User extends model{  /**   * Users role   *  /Public Function roles ()  {    //return $this Belongstomany (' App\models\role ');    return $this->belongstomany (' App\models\role ')->withpivot (' username ');}  }

The test code is as follows:

$user = User::find (1); foreach ($user->roles as $role) echo $role->pivot->username;//corresponding output: Matt 2 Matt 3

If you want your pivot table to automatically contain created_at and updated_at timestamps, use the Withtimestamps method when defining an association relationship:

return $this->belongstomany (' App\models\role ')->withtimestamps ();

Filtering association relationships through an intermediate table field

You can also use the Wherepivot and Wherepivotin methods to filter the result set returned by the Belongstomany when defining association relationships:

return $this->belongstomany (' App\models\role ')->withpivot (' username ')->wherepivot (' username ', ' Matt 2 ');// return $this->belongstomany (' App\models\role ')->wherepivotin (' role_id ', [1, 2]);

The test code is as follows:

$user = User::find (1);p Rint_r ($user->roles->toarray ());

The above corresponding output:

Array (  [0] = = Array    (      [id] = 3      [name] = Commander      [Created_at] = 2017-06-14 10:38:57      [ UPDATED_AT] = 2017-06-15 10:39:01      [pivot] = = Array        (          [user_id] = 1          [role_id] + 3          [ Username] (Matt 2)))    

If you want your pivot table to automatically contain created_at and updated_at timestamps, use the Withtimestamps method when defining an association relationship:

return $this->belongstomany (' App\models\role ')->withtimestamps ();