laravel7–模型关联
模型关联
PS：主表主键设为 id，关联主键默认就是 id，可以默认不写； PS：附表的外键设置为 user_id，即：主表名_主键，吻合可默认不写
一对一
//正相关联 User.php，一对一关联Profile表
namespace App\Http\Models;
use Illuminate\Database\Eloquent\Model;
class User extends Model
{
public function profile(){
//参数1或：'App\Http\Models\Profile' //参数2：默认为user_id，如不是需要指明
return $this->hasOne(Profile::class,'user_id','id');
}
}
//控制器下
$profiles = User::find(19)->profile;
return $profiles;
{"id":1,"user_id":19,"hobby":"\u559c\u6b22\u5927\u59d0\u59d0","status":1}
//反向关联
//一对一也支持反向关联：定向反向关联；具体通过在 Profile 设置即可；
//profile.php //参数1为主表类 //参数2，3和正向一致，默认对应可以不写
//App\Http\Models文件下
namespace App\Http\Models;
use Illuminate\Database\Eloquent\Model;
class Profile extends Model
{
public function user(){
return $this->belongsTo(User::class,'user_id','id');
}
}
//控制器文件下
$user = Profile::find(1)->user;
return $user;
//{"id":19,"username":"\u8721\u7b14\u5c0f\u65b0","password":"123","gender":"\u7537","email":"xiaoxin@163.com","price":"60.00","details":"123","uid":1001,"status":-1,"list":"{\"id\": 19, \"uid\": 1010}","deleted_at":null,"created_at":"2016-06-27T16:45:26.000000Z","updated_at":"2016-06-27T16:45:26.000000Z"}
一对多
//User.php对bool.php关联
public function book(){
return $this->hasMany(Book::class,'user_id','id');
}
//一对多
//得到蜡笔小新所有关联的书籍列表
$books = User::find(19)->book;
return $books;
[{"id":1,"user_id":19,"title":"\u300a\u838e\u58eb\u6bd4\u4e9a\u300b"},{"id":10,"user_id":19,"title":"\u300a\u70ed\u60c5\u5929\u5802\u300b"},{"id":11,"user_id":19,"title":"\u300a\u5b8c\u7f8e\u4eba\u751f\u300b"},{"id":29,"user_id":19,"title":"\u300a\u54c8\u5229\u6ce2\u7279\u300b"}]
//多对一
//book.php 关联 user.php model
public function user(){
return $this->belongsTo(User::class,'user_id','id');
}
//控制器
$users = Book::find(11)->user;
return $users;
{"id":19,"username":"\u8721\u7b14\u5c0f\u65b0","password":"123","gender":"\u7537","email":"xiaoxin@163.com","price":"60.00","details":"123","uid":1001,"status":-1,"list":"{\"id\": 19, \"uid\": 1010}","deleted_at":null,"created_at":"2016-06-27T16:45:26.000000Z","updated_at":"2016-06-27T16:45:26.000000Z"}
多对多关联
//正向
//user.php 关联 中间role_user 在关联 role
public function role(){
return $this->belongsToMany(
Role::class,
'role_user',
'user_id',
'role_id',
'id'
);
}
//关联关系
// 表user(主键id->user_id)->表role_user(role_id )->表role(id->role_id)
$users = User::find(19)->role;
return $users;
[{"id":2,"type":"\u8bc4\u8bba\u5ba1\u6838\u4e13\u5458","pivot":{"user_id":19,"role_id":2}},{"id":3,"type":"\u56fe\u7247\u76d1\u5bdf\u5458","pivot":{"user_id":19,"role_id":3}},{"id":1,"type":"\u8d85\u7ea7\u7ba1\u7406\u5458","pivot":{"user_id":19,"role_id":1}}]
//获取权限列表中某一个数据，和一对多操作方法一样，但注意返回的表名称
//注意，多对多这里role()返回的是role_user 表 //可以通过dd($roles)查看，所以，where 需要用role_id 来指明 #table: "role_user"
$roles = User::find(19)->role()->where('role_id', 1)->get();
$users = User::find(19)->role;
Illuminate\Database\Eloquent\Relations\BelongsToMany {#1070 ▼
#table: "role_user"
#foreignPivotKey: "user_id"
#relatedPivotKey: "role_id"
#parentKey: "id"
#relatedKey: "id"
#relationName: "role"
#pivotColumns: []
#pivotWheres: []
#pivotWhereIns: []
#pivotWhereNulls: []
#pivotValues: []
+withTimestamps: false
#pivotCreatedAt: null
#pivotUpdatedAt: null
#using: null
#accessor: "pivot"
#query: Illuminate\Database\Eloquent\Builder {#281 ▶}
#parent: App\Http\Models\User {#1069 ▶}
#related: App\Http\Models\Role {#276 ▶}
//也可以用集合的方法来处理
$users = User::find(19)->role;
//dd($users);
[{"id":2,"type":"\u8bc4\u8bba\u5ba1\u6838\u4e13\u5458","pivot":{"user_id":19,"role_id":2}},{"id":3,"type":"\u56fe\u7247\u76d1\u5bdf\u5458","pivot":{"user_id":19,"role_id":3}},{"id":1,"type":"\u8d85\u7ea7\u7ba1\u7406\u5458","pivot":{"user_id":19,"role_id":1}}]
$user = $users->where('id',1);
return $user;
{"2":{"id":1,"type":"\u8d85\u7ea7\u7ba1\u7406\u5458","pivot":{"user_id":19,"role_id":1}}}
//反向 多对多
// Role(主键id->role_id)->表role_user(role_id )->表role(id->user_id)
namespace App\Http\Models;
use Illuminate\Database\Eloquent\Model;
class Role extends Model
{
public function user(){
return $this->belongsToMany(
User::class,
'role_user',
'role_id',
'user_id'
);
}
}
$users = Role::find(1)->user;
return $users;
[{"id":24,"username":"\u5c0f\u660e","password":"123","gender":"\u7537","email":"xiaoming@163.com","price":"90.00","details":"123","uid":1004,"status":2,"list":null,"deleted_at":null,"created_at":"2016-08-27T23:50:52.000000Z","updated_at":"1997-01-01T01:01:01.000000Z","pivot":{"role_id":1,"user_id":24}},{"id":19,"username":"\u8721\u7b14\u5c0f\u65b0","password":"123","gender":"\u7537","email":"xiaoxin@163.com","price":"60.00","details":"123","uid":1001,"status":-1,"list":"{\"id\": 19, \"uid\": 1010}","deleted_at":null,"created_at":"2016-06-27T16:45:26.000000Z","updated_at":"2016-06-27T16:45:26.000000Z","pivot":{"role_id":1,"user_id":19}},{"id":99,"username":"\u795e\u56fd","password":"123","gender":"\u7537","email":"HUIYE@163.COM","price":"0.00","details":"123","uid":null,"status":0,"list":null,"deleted_at":null,"created_at":"2020-05-04T04:52:06.000000Z","updated_at":"2020-06-30T01:49:01.000000Z","pivot":{"role_id":1,"user_id":99}}]
//集合的方法取
$users = Role::find(1)->user;
$user = $users->where('id',19);
return $user;
{"1":{"id":19,"username":"\u8721\u7b14\u5c0f\u65b0","password":"123","gender":"\u7537","email":"xiaoxin@163.com","price":"60.00","details":"123","uid":1001,"status":-1,"list":"{\"id\": 19, \"uid\": 1010}","deleted_at":null,"created_at":"2016-06-27T16:45:26.000000Z","updated_at":"2016-06-27T16:45:26.000000Z","pivot":{"role_id":1,"user_id":19}}}
//user.php model
//多对多会生成一个中间字段：urr，里面包含多对多的双 id；
//想要 urr 字段包含更多的中间表字段，可以自行添加，还可以修改字段名
public function role(){
return $this->belongsToMany(
Role::class,
'role_user',
'user_id',
'role_id',
'id')
->withPivot('details','id')
->as('urr');
}
$users = User::find(19)->role;
return $users;
[{"id":2,"type":"\u8bc4\u8bba\u5ba1\u6838\u4e13\u5458","urr":{"user_id":19,"role_id":2,"details":"\u55ef","id":1}},{"id":3,"type":"\u56fe\u7247\u76d1\u5bdf\u5458","urr":{"user_id":19,"role_id":3,"details":"\u556a","id":6}},{"id":1,"type":"\u8d85\u7ea7\u7ba1\u7406\u5458","urr":{"user_id":19,"role_id":1,"details":"\u5566","id":8}}]
public function role(){
return $this->belongsToMany(
Role::class,
'role_user',
'user_id',
'role_id',
'id')
->withPivot('details','id')
->wherePivot('id',1)
->as('urr');
}
$users = User::find(19)->role;
return $users;
[{"id":2,"type":"\u8bc4\u8bba\u5ba1\u6838\u4e13\u5458","urr":{"user_id":19,"role_id":2,"details":"\u55ef","id":1}}]
关联查询
基本查询
$books = User::find(19)->book;
//$_books = User::find(19)->book()->toSql();
//"select * from `laravel_books` where `laravel_books`.`user_id` = ? and `laravel_books`.`user_id` is not null"
$_books = User::find(19)->book()->get();
//这两个查询相等
//可以采用where 筛选或闭包
$books = User::find(19)->book() ->where('id', 1)->orWhere('id', 11)->get();
//select * from `laravel_books` where `laravel_books`.`user_id` = ? and `laravel_books`.`user_id` is not null and `id` = ? or `id` = ?
$books = User::find(10)->book()->where(function ($query) { $query->where('id', 1)->orWhere('id', 11); })->get();
[{"id":1,"user_id":19,"title":"\u300a\u838e\u58eb\u6bd4\u4e9a\u300b"},{"id":11,"user_id":19,"title":"\u300a\u5b8c\u7f8e\u4eba\u751f\u300b"}]
//select * from `laravel_books` where `laravel_books`.`user_id` = ? and `laravel_books`.`user_id` is not null and (`id` = ? or `id` = ?)
has查询
//使用 has()方法，可以查询某些条件下的关联查询数据
//获取存在关联书籍的用户列表(言下之意：至少一本书)
//select * from `laravel_users` where exists (select * from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`)
$users = User::has('book')->get();
return $users;
//获取存在关联书籍(并超过3条)的用户列表
$users = User::has('book','>=',3)->toSql();
//select * from `laravel_users` where (select count(*) from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) >= 3
whereHas
//使用 whereHas()方法，创建闭包查询
$users = User::whereHas('book', function ($query) {
//这里$query是book 表，通过user_id 查询，返回user 表数据
$query->where('user_id', 19);
})->get();
whereHas()
//使用 whereHas()方法，创建闭包查询；
//whereHas闭包用法
$users = User::whereHas('book', function ($query) {
//这里$query是book 表，通过user_id 查询，返回user 表数据
$query->where('user_id', 19);
})->get();
//elect * from `laravel_users` where exists (select * from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id` and `user_id` = ?)
return $users;
[{"id":19,"username":"\u8721\u7b14\u5c0f\u65b0","password":"123","gender":"\u7537","email":"xiaoxin@163.com","price":"60.00","details":"123","uid":1001,"status":-1,"list":"{\"id\": 19, \"uid\": 1010}","deleted_at":null,"created_at":"2016-06-27T16:45:26.000000Z","updated_at":"2016-06-27T16:45:26.000000Z"}]
doesntHave
//使用 doesntHave()方法，即 has()的反向操作；
//获取不存在关联书籍的用户列表，闭包用法： whereDoesntHave()
$users = User::doesntHave('book')->toSql();
//
select * from `laravel_users` where not exists (select * from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`)
return $users;
withCount
//使用 withCount()方法，可以进行关联统计；
//关联统计，会自动给一个book_count 字段 //统计每个用户有多少本书
$users = User::withCount('book')->toSql(); return $users;
//select `laravel_users`.*, (select count(*) from `laravel_books` where `laravel_users`.`id` = `laravel_books`.`user_id`) as `book_count` from `laravel_users`
//给多个关系添加统计：profile_count，book_count
$users = User::withCount(['profile', 'book'])->get(); return $users;
//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`
$users = User::withCount(['profile', 'book' => function ($query) {
//这里限制被统计的记录
$query->where('user_id', 19);
}])
->get();
return $users;
//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` = ?) as `book_count` from `laravel_users`
[{"id":19,"username":"\u8721\u7b14\u5c0f\u65b0","password":"123","gender":"\u7537","email":"xiaoxin@163.com","price":"60.00","details":"123","uid":1001,"status":-1,"list":"{\"id\": 19, \"uid\": 1010}","deleted_at":null,"created_at":"2016-06-27T16:45:26.000000Z","updated_at":"2016-06-27T16:45:26.000000Z","profile_count":1,"book_count":4},
{"id":20,"username":"\u8def\u98de","password":"123","gender":"\u7537","email":"lufei@163.com","price":"70.00","details":"123","uid":1002,"status":0,"list":null,"deleted_at":null,"created_at":"2016-06-27T16:55:56.000000Z","updated_at":"1997-01-01T01:01:01.000000Z","profile_count":1,"book_count":0},{"id":21,"username":"\u9ed1\u5d0e\u4e00\u62a4","password":"456","gender":"\u7537","email":"yihu@163.com","price":"80.00","details":"123","uid":1003,"status":1,"list":null,"deleted_at":null,"created_at":"2016-07-27T17:22:16.000000Z","updated_at":"1997-01-01T01:01:01.000000Z","profile_count":1,"book_count":0},
.....
模型预加载
预加载
预加载，就是解决关联查询中产生的 N+1 次查询带来的资源消耗我们要获取所有书籍的作者(或拥有者)，普通查询方案如下
//获取所有书籍列表
$books = Book::all();
//遍历每一本书
foreach ($books as $book) {
//每一本书的关联用户的姓名
DebugBar::info($book->user->username);
}
PS：通过调试器 Debugbar 中 SQL 语句的分析，发现包含十多条 SQL 语句； PS：原因是关联查询时，每遍历一次就会执行一遍 SQL 语句，导致性能欠佳； PS：所谓 N+1 条，就是起初获取全部数据的 1 条和，遍历的 N 条
使用 with()关键字，进行预载入设置，提前将 SQL 整合；
//with关键字预载入
$books = Book::with('user')->get();
foreach ($books as $book) {
DebugBar::info($book->user->username);
}
PS：此时的 SQL 执行数目为：1+1 条；也支持数组多个关联 with[‘book’,‘prifile’]；
PS：预加载也可以设置显示的列
//预载入设置指定的列
$books = Book::with('user:id,username')->get();
foreach ($books as $book) {
DebugBar::info($book->user->username);
}
//可以看出sql执行的条目会大大的减少
如果是每次都必须进行关联查询，可以在模型中定义
//book.php model
protected $with = ['user'];
这样就不需要使用with()方法了
#演示 取消模型$with,看预下载结合筛选；
$books = Book::with(['user' => function ($query) {
$query->where('id', 19);
}])->get();
PS:预载入筛选不可以使用 limit、take 方法；
有时可能产生逻辑判断是否查询数据，但预加载会提前关联执行；
这样会导致资源性能的浪费，此时可以采用延迟预载入；
$books = Book::all();
//if逻辑判断
if (true) {
$books = $books->load('user');
//load(['user' => function () {}])
foreach ($books as $book) {
DebugBar::info($book->user->username);
}
}
同理loadCount()方法，可以实现关联统计；
$users = User::all();
if (true) {
return $users->loadCount('book');
}
