Home > Article > PHP Framework > Laravel introductory tutorial: The relationship between tables
##First of all, about the relationship between tables
1.One-to-one2.One-to-many3.Many-to-one4.Many-to-manyDistinguish between parent table and child table
1. The "one" side is the parent table2. The "many" side It is a child tableHow to deal with one-to-many relationships
Create a field (foreign key) in the child table to point to the parent table
How to deal with many-to-many relationships
Create an intermediate table to convert the "many-to-many" relationship into "one-to-many"
Case analysis
Table 1: User table (it_user)Table 2: User details table (it_user_info)Table 3: Article table (it_article)Table 4: Country table (it_country)Table 5: User role table (it_role)① One-to-oneThe user table (Table 1) and the details table (Table 2) have a one-to-one relationship②One-to-manyThe user table (Table 1) and the article table ( Table 3) is a one-to-many relationship③Many-to-oneThe user table (Table 1) and the country table (Table 4) are a many-to-one relationship ④Many-to-manyThe user table (Table 1) and the role table (Table 5) are a many-to-many relationshipUser table creation and test data
DROP TABLE IF EXISTS `it_user`; CREATE TABLE `it_user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `name` varchar(64) DEFAULT NULL COMMENT '用户名', `password` char(32) DEFAULT NULL COMMENT '密码(不使用md5)', `country_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `国家id` (`country_id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of it_user -- ---------------------------- INSERT INTO `it_user` VALUES ('1', 'xiaoming', '123456', '1'); INSERT INTO `it_user` VALUES ('2', 'xiaomei', '123456', '1'); INSERT INTO `it_user` VALUES ('3', 'xiaoli-new', '123', '1');
User details table creation and test data
-- ---------------------------- -- Table structure for it_user_info -- ---------------------------- DROP TABLE IF EXISTS `it_user_info`; CREATE TABLE `it_user_info` ( `user_id` int(11) NOT NULL AUTO_INCREMENT, `tel` char(11) DEFAULT NULL, `email` varchar(128) DEFAULT NULL, `addr` varchar(255) DEFAULT NULL, PRIMARY KEY (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of it_user_info -- ---------------------------- INSERT INTO `it_user_info` VALUES ('1', '13012345678', 'xiaoming@163.com', '北京'); INSERT INTO `it_user_info` VALUES ('2', '15923456789', 'xiaomei@163.com', '上海'); INSERT INTO `it_user_info` VALUES ('3', '18973245670', 'xiaoli@163.com', '武汉');
Article table creation and test data
-- ---------------------------- -- Table structure for it_article -- ---------------------------- DROP TABLE IF EXISTS `it_article`; CREATE TABLE `it_article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, `content` text, `user_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of it_article -- ---------------------------- INSERT INTO `it_article` VALUES ('1', '世界那么大,我想去看看', '钱包那么小,总是不够', '1'); INSERT INTO `it_article` VALUES ('2', '我想撞角遇到爱', '却是碰到鬼', '2'); INSERT INTO `it_article` VALUES ('3', '哈哈哈哈', '嘻嘻嘻嘻', '1');
Country Table creation and test data
-- ---------------------------- -- Table structure for it_country -- ---------------------------- DROP TABLE IF EXISTS `it_country`; CREATE TABLE `it_country` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of it_country -- ---------------------------- INSERT INTO `it_country` VALUES ('1', '中国'); INSERT INTO `it_country` VALUES ('2', '美国');
User role table creation and test data
-- ---------------------------- -- Table structure for it_role -- ---------------------------- DROP TABLE IF EXISTS `it_role`; CREATE TABLE `it_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of it_role -- ---------------------------- INSERT INTO `it_role` VALUES ('1', '开发'); INSERT INTO `it_role` VALUES ('2', '测试'); INSERT INTO `it_role` VALUES ('3', '管理');
User and role intermediate table creation and testing Data
-- ---------------------------- -- Table structure for it_user_role -- ---------------------------- DROP TABLE IF EXISTS `it_user_role`; CREATE TABLE `it_user_role` ( `user_id` int(11) DEFAULT NULL, `role_id` int(11) DEFAULT NULL, KEY `role_id` (`role_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of it_user_role -- ---------------------------- INSERT INTO `it_user_role` VALUES ('1', '1'); INSERT INTO `it_user_role` VALUES ('1', '2'); INSERT INTO `it_user_role` VALUES ('1', '3'); INSERT INTO `it_user_role` VALUES ('2', '1'); INSERT INTO `it_user_role` VALUES ('3', '2');
Preparation work
1. Planning routing
In routes/ Write the following route under web.php://ORM的关联关系 Route::get('/orm/relation/{mode}','ORM\UserController@relation');
2. Write the relation method in App/Http/Controllers/ORM/UserController.php
public function relation($mode) { switch ($mode){ case '1_1': { //一对一 } break; case '1_n': { //一对多 } break; case 'n_1': { //多对一 } break; case 'n_n': { //多对多 } break; default; } }
3. Install the debug debugging tool
3.1 Use the composer command to installcomposer require barryvdh/laravel-debugbar3.2. Modify the config/app.php file, load debugbar to laravel into the project, and add it to the 'providers' array The following configuration:
Barryvdh\Debugbar\ServiceProvider::class,In addition to installing the debugbar debugging tool, you can also use query monitoring: Add the following code to the boot method of providers/AppServiceProvider.php
\DB::listen(function ($query) { var_dump($query->sql); var_dump($query->bindings); echo '<br>'; });You can also print out the executed sql statement.
One-on-one
1. Create the Userinfo model object
Enter the cmd command line. Executing the following command in the directory where the laravel project is locatedphp artisan make:model Userinfowill generate Userinfo.php in the App directory
2. Edit the Userinfo model file
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Userinfo extends Model { protected $table = 'user_info'; protected $primaryKey = 'user_id'; protected $fillable = ['user_id','tel','email','addr']; public $timestamps = false; }
3. Write UserModel and add one-to-one method
<?php namespace App; use Illuminate\Database\Eloquent\Model; class UserModel extends Model { protected $table = 'user';//真是表名 protected $primaryKey = 'id';//主键字段,默认为id protected $fillable = ['name','password'];//可以操作的字段 public $timestamps = false;//如果数据表中没有created_at和updated_id字段,则$timestamps则可以不设置, 默认为true public function Userinfo() { /* * @param [string] [name] [需要关联的模型类名] * @param [string] [foreign] [参数一指定数据表中的字段] * */ return $this->hasOne('App\Userinfo','user_id'); }
4. Write UserController and call one-to-one method
public function relation($mode) { switch ($mode){ case '1_1': { //一对一 $data = UserModel::find(1)->Userinfo()->get(); dd($data); } break; default; } }
One-to-many
1. Create the article model object
Execute the commandphp artisan make:model ArticleGenerate the Article.php file under the app
2. Write article model file
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Article extends Model { protected $table = 'article'; protected $primaryKey = 'id'; protected $fillable = ['id','title','content','user_id']; public $timestamps = false; }
3. Write UserModel and add one-to-many method
public function Artice() { return $this->hasMany('App\Article','User_id'); }
4. Write UserController and call one-to-many method
public function relation($mode) { switch ($mode){ case '1_1': { //一对一 $data = UserModel::find(1)->Userinfo()->get(); dd($data); } break; case '1_n': { //一对多 $data = UserModel::find(1)->Artice()->get(); dd($data); } break; default; } } }
many-to-one
1. Create country model Object
executes the commandphp artisan make:model Country
2. Write the country model file
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Country extends Model { protected $table = 'country'; //真实表名 protected $primaryKey = "id"; //主键id protected $fillable = ['id','name']; //允许操作的字段 public $timestamps = false; //如果数据表中没有created_at和updated_id字段,则$timestamps则可以不设置, 默认为true }
3. Write the UserModel and add many-to-one Method
public function Country() { return $this->belongsTo('App\Country','country_id'); }
4. Write UserController and call method
public function relation($mode) { switch ($mode){ case '1_1': { //一对一 $data = UserModel::find(1)->Userinfo()->get(); dd($data); } break; case '1_n': { //一对多 $data = UserModel::find(1)->Artice()->get(); dd($data); } break; case 'n_1': { //多对一 $data = UserModel::find(1)->Country()->get(); dd($data); } break; default; } } }
Many-to-Many
1. Create role model object
Execute commandphp artisan make:model RoleExecute command
php artisan make:model User_role
2.Write Role model
<?php namespace App; use Illuminate\Database\Eloquent\Model; class Role extends Model { protected $table = 'role'; protected $primaryKey = "id"; protected $fillable = ['name']; public $timestamps =false; }Write the User_role modelBecause there is no primary key field in the table, two fields are needed
<?php namespace App; use Illuminate\Database\Eloquent\Model; class User_role extends Model { protected $table = 'user_role'; public $timestamps =false; }
3. Write the UserModel and add the many-to-many method
public function Role(){ /* * 第一个参数:要关联的表对应的类 * 第二个参数:中间表的表名 * 第三个参数:当前表跟中间表对应的外键 * 第四个参数:要关联的表跟中间表对应的外键 * */ return $this->belongsToMany('App\Role','user_role','user_id','role_id'); }
4. Write UserController and call the many-to-many method
public function relation($mode) { switch ($mode){ case '1_1': { //一对一 $data = UserModel::find(1)->Userinfo()->get(); dd($data); } break; case '1_n': { //一对多 $data = UserModel::find(1)->Artice()->get(); dd($data); } break; case 'n_1': { //多对一 $data = UserModel::find(1)->Country()->get(); dd($data); } break; case 'n_n': { //多对多 $data = UserModel::find(2)->Role()->get(); dd($data); } break; default; } }
Summary:
1. One pair One usage method: hasOne()2. One-to-many usage method: hasMany()3. Many-to-one usage method: belongsTo()4. Many How to use many: belongsToMany()PHP Chinese website, a large number of freelaravel introductory tutorials, welcome to learn online!
This article is reproduced from: https://blog.csdn.net/weixin_38112233/article/details/79220535The above is the detailed content of Laravel introductory tutorial: The relationship between tables. For more information, please follow other related articles on the PHP Chinese website!