正文 【Laravel基础】10. 模型的关联查询 拾年之璐 V管理员 /2021年 /283 阅读 0210 ## 10. 模型的关联查询 [TOC] ### 10.1 关联概念 关联模型,即:两张或以上的表进行一定规则的绑定关联。 比如: + 一个学生(学生表)对应一张个人信息卡(信息表),这种就是一对一; + 一篇博文(帖子表)对应多个评论(评论表),这种就是一对多; + 一个用户(用户表)对应多个职位(权限表),而一个职位又可以有多个用户;那么,这种就是多对多关联; 当然,还有更多更复杂的关联,都是基于此的。 本文只探讨这三种基本的关联。 既然是关联,当然会有绑定的概念,当有数据库操作,关联表也会跟着变动;这就是关联模型的意义。 ### 10.2 一对一关联 1、我们以下面的两张表为实例,进行演示:  > 左侧`users`为`主表`,`主键为id`。在Laravel中,主键默认是id。 > > 右侧`profiles`为`附表`,主键为id,`外键为user_id`。在laravel中,外键模式格式是`主表名_主键`。 然后使用命令,创建两个表对应的模型model:`User.php` 和`Profile.php` ```php php artisan make:model Models/User php artisan make:model Models/Profile ``` 然后给两个表`生成注释`: ```php php artisan ide-helper:models ``` 2、`正向关联`:在主表`User.php`中,写入关联附表`Profile.php`的代码,格式及参数解释,如下所示: ```php //User.php,一对一关联Profile 表 public function profile() { //参数1 或:'App\Http\Models\Profile' //参数2:默认为user_id,如不是需要指明 //参数3:默认id,如不是需要指明 return $this->hasOne(Profile::class, 'user_id', 'id'); } ``` 然后,即可在控制类中使用: ```php //注意:->profile 不要加括号,以属性方式访问 $profiles = User::find(19)->profile; return $profiles; ``` 输出结果: ```json { "id": 1, "user_id": 19, "hobby": "喜欢大姐姐", "status": 1 } ``` 在这个过程中,执行的SQL语句是两条,为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select * from `laravel_profiles` where `laravel_profiles`.`user_id` = 19 and `laravel_profiles`.`user_id` is not null limit 1 ``` 3、`反向关联`:在附表`Profile.php`中,写入关联主表`User.php`的代码,格式及参数如下所示: ```php public function user() { //参数1 为主表类 //参数2,3 和正向一致,默认对应可以不写 return $this->belongsTo(User::class, 'user_id', 'id'); } ``` 然后可以在控制类中使用: ```php $users = Profile::find(1)->user; return $users; ``` 输出结果: ```php { "id": 19, "username": "蜡笔小新", "password": "123", "gender": "男", ...... } ``` 在这个过程中,执行的SQL语句为: ```sql select * from `laravel_profiles` where `laravel_profiles`.`id` = 1 limit 1 select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 ``` ### 10.3 一对多关联 1、我们以下面的两张表进行演示:  > 可以看出,这是一个`一对多`的关联。 同样,为表books 创建模型: ```php php artisan make:model Models/Book ``` 并且生成注释: ```php php artisan ide-helper:models ``` 2、`正向关联`:在主表`User.php`中,写入关联附表`Book.php`的代码,格式及参数解释,如下所示: ```php //正向,一对多关联Book 表 public function book() { return $this->hasMany(Book::class, 'user_id', 'id'); } ``` 然后,即可在控制类中使用: ```php //得到蜡笔小新所有关联的书籍列表 $books = User::find(19)->book; return $books; ``` 执行结果: ```json [ { "id": 1, "user_id": 19, "title": "《莎士比亚》" }, { "id": 10, "user_id": 19, "title": "《热情天堂》" }, { "id": 11, "user_id": 19, "title": "《完美人生》" }, { "id": 29, "user_id": 19, "title": "《哈利波特》" } ] ``` 在这个过程中,执行的SQL语句是两条: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select * from `laravel_books` where `laravel_books`.`user_id` = 19 and `laravel_books`.`user_id` is not null ``` 3、获取一对多关联的数据,如果再进行筛选,可以使用下面方法: ```php $books = User::find(19)->book()->where('id',11)->get(); return $books; ``` 执行结果为: ```json [ { "id": 11, "user_id": 19, "title": "《完美人生》" } ] ``` 执行的SQL为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select * from `laravel_books` where `laravel_books`.`user_id` = 19 and `laravel_books`.`user_id` is not null and `id` = 11 ``` 4、`反向关联`:在附表`Book.php`中,写入关联主表`User.php`的代码。和一对一的反向关联一致。 ```php public function user() { //参数1 为主表类 //参数2,3 和正向一致,默认对应可以不写 return $this->belongsTo(User::class, 'user_id', 'id'); } ``` 然后在控制类中执行代码: ```php //一对多反向关联 $users = Book::find(1)->user; return $users; ``` 执行结果为: ```json { "id": 19, "username": "蜡笔小新", "password": "123", "gender": "男", ...... } ``` 执行的SQL为: ```sql select * from `laravel_books` where `laravel_books`.`id` = 1 limit 1 select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 ``` ### 10.4 多对多关联 1、多对多关联,比前面两种要复杂一些,需要一张中间表,共三张; 我们以下面的 3 张表进行演示:  > 左表:.users:用户表 > > 中表:.role_users:中间表,默认表名是这样的。然后两边互相关联的默认外键:user_id,role_id。 > > 右表:.roles:权限表 对于这三张表,只需要创建`用户表`和`权限表`的模型即可: ```php php artisan make:model Models/User php artisan make:model Models/Role ``` 同样,写入注释: ```php php artisan ide-helper:models ``` 2、`正向关联`:在 `User.php` 设置`多对多`关联 ```php //多对多关联 public function role() { //参数1:同上 //参数2:中间表名 //参数3,4 如果是默认值,则可不传 return $this->belongsToMany(Role::class, 'role_users', 'user_id', 'role_id'); } ``` Role.php留空。 然后即可在控制类中使用。比如多对多的关联输出:查看用户19都拥有哪些权限 ```php $roles = User::find(19)->role; return $roles; ``` 输出结果: ```json [ { "id": 2, "type": "评论审核专员", "pivot": { "user_id": 19, "role_id": 2 } }, { "id": 3, "type": "图片监察员", "pivot": { "user_id": 19, "role_id": 3 } }, { "id": 1, "type": "超级管理员", "pivot": { "user_id": 19, "role_id": 1 } } ] ``` > 多对多会生成一个中间字段:pivot,里面包含多对多的双id; 执行的SQL为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select `laravel_roles`.*, `laravel_role_users`.`user_id` as `pivot_user_id`, `laravel_role_users`.`role_id` as `pivot_role_id` from `laravel_roles` inner join `laravel_role_users` on `laravel_roles`.`id` = `laravel_role_users`.`role_id` where `laravel_role_users`.`user_id` = 19 ``` 3、获取权限列表中某一个数据,和一对多操作方法一样,但注意返回的表名称; ```php //注意,多对多这里role()返回的是role_user 表 //可以通过dd($roles)查看,所以,where 需要用role_id 来指明 $roles = User::find(19)->role()->where('role_id', 1)->get(); return $roles; //当然,你也可以使用集合的方式去实现筛选 $roles = User::find(19)->role; return $roles->where('id', 1); ``` 执行结果: ```php [ { "id": 1, "type": "超级管理员", "pivot": { "user_id": 19, "role_id": 1 } } ] ``` 执行的SQL为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select `laravel_roles`.*, `laravel_role_users`.`user_id` as `pivot_user_id`, `laravel_role_users`.`role_id` as `pivot_role_id` from `laravel_roles` inner join `laravel_role_users` on `laravel_roles`.`id` = `laravel_role_users`.`role_id` where `laravel_role_users`.`user_id` = 19 and `role_id` = 1 ``` 4、`反向关联`:多对多的反向关联和其它两种方式也差不多,在模型`Role.php`中: ```php //反向多对多关联,后面id 是反的 public function user() { return $this->belongsToMany(User::class, 'role_users', 'role_id' , 'user_id'); } ``` 然后在控制类中执行: ```php $users = Role::find(1)->user; return $users; ``` 执行结果如下,即查询拥有权限`1`(超级管理员)的用户: ```json [ { "id": 24, "username": "小明", "password": "123", ...... "pivot": { "role_id": 1, "user_id": 24 } }, { "id": 19, "username": "蜡笔小新", "password": "123", ...... "pivot": { "role_id": 1, "user_id": 19 } }, { "id": 99, "username": "辉夜", "password": "123", ...... "pivot": { "role_id": 1, "user_id": 99 } } ] ``` 执行的SQL为: ```sql select * from `laravel_roles` where `laravel_roles`.`id` = 1 limit 1 select `laravel_users`.*, `laravel_role_users`.`role_id` as `pivot_role_id`, `laravel_role_users`.`user_id` as `pivot_user_id` from `laravel_users` inner join `laravel_role_users` on `laravel_users`.`id` = `laravel_role_users`.`user_id` where `laravel_role_users`.`role_id` = 1 ``` 5、多对多会生成一个中间字段:`pivot`,里面包含`多对多的双id`。 如果想要pivot 字段包含更多的中间表字段,可以自行添加,还可以修改字段名。 比如正向关联中,修改为: ```php //多对多关联 public function role() { //参数1:同上 //参数2:中间表名 //参数3,4 如果是默认值,则可不传 return $this->belongsToMany(Role::class, 'role_users', 'user_id', 'role_id') ->withPivot('details', 'id') ->as('pivot_name'); } ``` 然后执行: ```php $roles = User::find(19)->role()->where('role_id', 1)->get(); return $roles; ``` 执行结果为: ```json [ { "id": 1, "type": "超级管理员", "pivot_name": { "user_id": 19, "role_id": 1, "details": "啦", "id": 8 } } ] ``` 执行的SQL为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select `laravel_roles`.*, `laravel_role_users`.`user_id` as `pivot_user_id`, `laravel_role_users`.`role_id` as `pivot_role_id`, `laravel_role_users`.`details` as `pivot_details`, `laravel_role_users`.`id` as `pivot_id` from `laravel_roles` inner join `laravel_role_users` on `laravel_roles`.`id` = `laravel_role_users`.`role_id` where `laravel_role_users`.`user_id` = 19 and `role_id` = 1 ``` 6、定义多对多绑定时,可以在绑定方法内筛选数据; 比如正向关联中,修改为: ```php //多对多关联 public function role() { //参数1:同上 //参数2:中间表名 //参数3,4 如果是默认值,则可不传 return $this->belongsToMany(Role::class, 'role_users', 'user_id', 'role_id') ->wherePivot('id', 1); } ``` > 还有wherePivotIn,以及派生的四种方法。 然后执行: ```php $roles = User::find(19)->role; return $roles; ``` 输出结果为: ```json [ { "id": 2, "type": "评论审核专员", "pivot": { "user_id": 19, "role_id": 2 } } ] ``` 执行的SQL为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select `laravel_roles`.*, `laravel_role_users`.`user_id` as `pivot_user_id`, `laravel_role_users`.`role_id` as `pivot_role_id` from `laravel_roles` inner join `laravel_role_users` on `laravel_roles`.`id` = `laravel_role_users`.`role_id` where `laravel_role_users`.`user_id` = 19 and `laravel_role_users`.`id` = 1 ``` > 除了一对一,一对多,多对多,还有派生的远程一对一,远程一对多,以及多态一对一,多态一对多,多态多对多。 ### 10.5 关联查询 前文讲述了三种常用的关联查询。本节讲述几种常用查询方案。 1、下面两种查询方式是一样的: ```php //下面两种查询是一样的; $books = User::find(19)->book; $books = User::find(19)->book()->get(); ``` 2、可以采用where 筛选或闭包。 如: ```php books = User::find(19)->book() ->where('id', 1)->orWhere('id', 11)->get(); //或者,执行结果相同 $books = User::find(19)->book()->where(function ($query) { $query->where('id', 1)->orWhere('id', 11); })->get(); ``` 执行结果: ```json [ { "id": 1, "user_id": 19, "title": "《莎士比亚》" }, { "id": 11, "user_id": 19, "title": "《完美人生》" } ] ``` 执行的SQL为: ```sql select * from `laravel_users` where `laravel_users`.`id` = 19 limit 1 select * from `laravel_books` where `laravel_books`.`user_id` = 19 and `laravel_books`.`user_id` is not null and `id` = 1 or `id` = 11 ``` 3、使用has()方法,可以查询某些条件下的关联查询数据。如: ```php //示例1:获取存在关联书籍的用户列表(言下之意:至少一本书) $users = User::has('book')->get(); return $users; //示例2:获取存在关联书籍(并超过3 条)的用户列表 $users = User::has('book','>=', 3)->get(); return $users; ``` 执行的SQL分别为: ```sql /*示例1:*/ select * from `laravel_users` where exists (select * from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) /*示例2:*/ select * from `laravel_users` where (select count(*) from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) >= 3 ``` 执行结果分别为: ```json //示例1结果: [ { "id": 19, "username": "蜡笔小新", "password": "123", ...... }, { "id": 20, "username": "路飞", "password": "123", ...... }, { "id": 21, "username": "黑崎一护", "password": "456", ...... }, { "id": 24, "username": "小明", "password": "123", ...... }, { "id": 25, "username": "孙悟饭", "password": "123", ...... }, ...... ] //示例2结果: [ { "id": 19, "username": "蜡笔小新", "password": "123", ...... } ] ``` 4、使用whereHas()方法,创建闭包查询; ```php //whereHas 闭包用法,返回user 表数据 $users = User::whereHas('book', function ($query) { //这里$query 是book 表,通过 book表的 id 查询 $query->where('id', 2); })->get(); //可以理解为查询写书id=2的人是谁。 return $users; ``` 执行的SQL为: ```sql select * from `laravel_users` where exists (select * from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id` and `id` = 2) ``` 5、使用doesntHave()方法,即has()的反向操作: ```php //获取不存在关联书籍的用户列表,闭包用法:whereDoesntHave() $users = User::doesntHave('book')->get(); return $users; ``` 执行的SQL为: ```sql select * from `laravel_users` where not exists (select * from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) ``` 6、使用withCount()方法,可以进行关联统计; 如: ```php //关联统计,会自动给一个book_count 字段 //统计每个用户有多少本书 $users = User::withCount('book')->get(); return $users; ``` 执行的SQL为: ```sql select `laravel_users`.*, (select count(*) from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) as `book_count` from `laravel_users` ``` 再如: ```php //给多个关系添加统计:profile_count,book_count $users = User::withCount(['profile', 'book'])->get(); return $users; ``` 执行的SQL为: ```sql select `laravel_users`.*, (select count(*) from `laravel_profiles` where `laravel_users`.`id` = `laravel_profiles`.`user_id`) as `profile_count`, (select count(*) from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) as `book_count` from `laravel_users` ``` 再如: ```php //关联统计再结合闭包进行筛选,还可以设置别名 $users = User::withCount(['profile', 'book' => function ($query) { //这里限制被统计的记录,即只查询表book中user_id=19的数据和 $query->where('user_id', 19); }])->get(); return $users; ``` 执行的SQL为: ```sql select `laravel_users`.*, (select count(*) from `laravel_profiles` where `laravel_users`.`id` = `laravel_profiles`.`user_id`) as `profile_count`, (select count(*) from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id` and `user_id` = 19) as `book_count` from `laravel_users` ``` 以上。 本文采用创作共用版权 CC BY-NC-SA 3.0 CN 许可协议,转载或复制请注明出处! -- 展开阅读全文 --