Laravel 模型关联、关联查询、预加载使用实例 · php开发笔记

数据表通常相互关联，通常简单一些的可以使用多表联查进行查询，对于更加复杂的使用laravel的模型关联查询更加方便简单，[中文文档](https://learnku.com/docs/laravel/7.x/eloquent-relationships/7500)，下面以省市两张表为例 ## 前期准备：首先创建一个PlaceController控制器并创建index方法： ~~~ <?php namespace App\Http\Controllers\Api; use Illuminate\Http\Request; use App\Http\Controllers\Controller; class PlaceController extends Controller { public function index(Request $request) { } } ~~~ 然后创建分别创建省市两个模型(model): ~~~ <?php namespace App\Http\Models; use Illuminate\Database\Eloquent\Model; class Province extends Model { protected $table = 'province'; protected $primaryKey = 'id'; public $timestamps = false; } ~~~ ~~~ <?php namespace App\Http\Models; use Illuminate\Database\Eloquent\Model; class City extends Model { protected $table = 'city'; protected $primaryKey = 'id'; public $timestamps = false; } ~~~ 准备工作结束，要使用模型关联必须先定义关联关系即在模型中定义关联方法。 ## 一对一 :`hasOne()` 在Province模型中定义与City模型的关联关系city()： ~~~ public function city() { return $this->hasOne('App\Http\Models\City', 'pid', 'id'); //参数一:需要关联的子表类名,前面必须加上命名空间参数二:子表关联父表的字段参数三:父表关联子表的字段 } ~~~ 在PlaceController控制器的index()中使用: ~~~ public function index(Request $request) { $place = Province::select('*') ->with(['city' => function($query) { //city对应上面province模型中定义的city方法名闭包内是子查询 return $query->select('*'); }]) ->where('id', 11)->first()->toArray(); //查询id为11的省份及该省份下的第一个城市 dd($place); } ~~~ ~~~ array:3 [▼ "id" => 11 "pname" => "浙江省" "city" => array:3 [▼ "id" => 89 "pid" => 11 "cname" => "台州市" ] ] ~~~ 除此之外,在子查询语句中常规的where、orderBy等方法都可以使用： ~~~ public function index(Request $request) { $cid = 99; $place = Province::select('*') ->with(['city' => function($query) use ($cid) { //传参使用use() 多个参数($cid, $name) return $query->select('*')->where('id', $cid); }]) ->where('id', 11)->first()->toArray(); dd($place); } ~~~ ~~~ array:3 [▼ "id" => 11 "pname" => "浙江省" "city" => array:3 [▼ "id" => 99 "pid" => 11 "cname" => "杭州市" ] ] ~~~ ## 一对一定义反向关联:belongsTo() 上面是从省份查下面的城市，反向关联就是从城市查所属身份，首先在city模型中定时province(): ~~~ public function province() { return $this->belongsTo('App\Http\Models\Province', 'pid', 'id'); //参数一:需要关联的父表类名,前面必须加上命名空间注意:参数二:子表关联父表的字段参数三:父表关联子表的字段 } ~~~ 在控制器中使用: ~~~ public function index(Request $request) { $place = City::select('*') ->with(['province' => function($query) { return $query->select('*'); }]) ->where('id', 99)->first()->toArray(); dd($place); } ~~~ ~~~ array:4 [▼ "id" => 99 "pid" => 11 "cname" => "杭州市" "province" => array:2 [▼ "id" => 11 "pname" => "浙江省" ] ] ~~~ ## 一对多:hasMany() 在province模型中定义citys(): ~~~ public function citys() { return $this->hasMany('App\Http\Models\City', 'pid', 'id'); } ~~~ 在控制器中使用: ~~~ public function index(Request $request) { $place = Province::select('*') ->with(['citys' => function($query) { return $query->select('*'); }]) ->where('id', 11)->first()->toArray(); //查询id为11的省份及该省下所有的城市 dd($place); } ~~~ ~~~ array:3 [▼ "id" => 11 "pname" => "浙江省" "citys" => array:11 [▼ 0 => array:3 [▼ "id" => 89 "pid" => 11 "cname" => "台州市" ] 1 => array:3 [▶] 2 => array:3 [▶] 3 => array:3 [▶] 4 => array:3 [▶] 5 => array:3 [▶] 6 => array:3 [▶] 7 => array:3 [▶] 8 => array:3 [▶] 9 => array:3 [▶] 10 => array:3 [▶] ] ] ~~~ ## 一对多反向关联:belongsTo() ~~~ public function provinces() { return $this->belongsTo('App\Http\Models\Province', 'pid', 'id'); //参数一:需要关联的父表类名,前面必须加上命名空间注意:参数二:子表关联父表的字段参数三:父表关联子表的字段 } ~~~ 和一对一的反向关联相同就不多说了 ## 多对多:belongsToMany() 多对多比上面的都要复杂一些,需要用三张表演示,如role(角色表)，`role_authority(角色对应的权限表)` 和 `authority(所有权限表),其中role表(爷爷级)和role_authority(父级中间表)表的关联字段是role_id, role_authority(父级中间表)表和authority(孙子级)表关联的字段是authority_id, 那么就可以在role(爷爷级)表中定义关联关系了:` ~~~ public function role() { return $this->belongsToMany('App\Http\Models\AdminAuthority', 'admin_role_authority', 'role_id', 'authority_id'); //参数一:最低一级的表类名(孙子级) 参数二:中间表(父级)的表名参数三:中间表与他的父级表的关联字段参数四:中间表与他的儿子级表的关联字段 } ~~~ ~~~ $role = AdminRole::select('*') ->with(['role' => function($query) { return $query->select('*'); }]) ->where('role_id', 22)->first()->toArray(); dd($role); ~~~ ~~~ array:4 [▼ "role_id" => 22 "role_name" => "广告设计师" "description" => "广告设计" "role" => array:7 [▼ 0 => array:11 [▼ "authority_id" => 88 "name" => "广告管理" "is_active" => 1 "controller" => null "action" => null "url" => null "icon" => "Hui-iconfont-dangan" "parent_id" => 0 "sort" => 3 "role_id" => 22 "pivot" => array:2 [▼ "role_id" => 22 "authority_id" => 88 ] ] 1 => array:11 [▶] 2 => array:11 [▶] 3 => array:11 [▶] 4 => array:11 [▶] 5 => array:11 [▶] 6 => array:11 [▶] ] ] ~~~ ## 敲黑板划重点: 1.关联模型查询的实质总共执行两条SQL语句,打印一下执行的SQL语句: ~~~ DB::connection()->enableQueryLog();#开启执行日志 $role = AdminRole::select('*') ->with(['role' => function($query) { return $query->select('*'); }]) ->where('role_id', 22)->first()->toArray(); dd(DB::getQueryLog()); //获取查询语句、参数和执行时间 ~~~ ~~~ array:2 [▼ 0 => array:3 [▼ "query" => "select * from `admin_role` where `role_id` = ? limit 1" "bindings" => array:1 [▼ 0 => 22 ] "time" => 48.82 ] 1 => array:3 [▼ "query" => "select *, `admin_role_authority`.`role_id` as `pivot_role_id`, `admin_role_authority`.`authority_id` as `pivot_authority_id` from `admin_authority` inner join `admin_role_authority` on `admin_authority`.`authority_id` = `admin_role_authority`.`authority_id` where `admin_role_authority`.`role_id` in (22) ◀" "bindings" => [] "time" => 9.41 ] ] ~~~ 2.在关联查询中,select()如果不是查出全部字段,要查询的字段必须包含表和表之间的关联字段才可以使用: ~~~ $role = User::select('id', 'name') //主表关联字段是id ->with(['role' => function($query) { return $query->select('user_id', 'name'); //子表关联字段是user_id }]) ->first()->toArray(); ~~~ 3.另外还可以组合更加复杂的查询,如三张表A、B、C，A表和B表一对一，A表和C表一对多，就可以使用leftjoin加关联模型一对多查询： ~~~ public function items() { return $this->hasMany('App\Http\Models\A', 'aid', 'id'); } ~~~ ~~~ MallOrder::from('A as a') ->leftJoin('B as b','a.id','=','b.aid') ->with(['items' => function($query) { return $query->select('*'); }])->group()->select('*'); ~~~ 类似上面的组合需要打开脑洞去想去实践了,模型关联查询越用越顺手,越用越觉得好用,简直是居家旅行,装B神器!