laravel学习笔记之模型关联预加载
说明：本文主要说明laravel eloquent的延迟预加载(eager loading)，使用延迟预加载来减少mysql查询次数。同时，作者会将开发过程中的一些截图和代码黏上去，提高阅读效率。
备注：现在有4张表：商家表merchants、商家电话表phones、商家拥有的店铺shops表和店铺里的商品表products。并且关系是：
[ 'merchants_phones' => 'one-to-one', 'merchants_shops' => 'one-to-many', 'shops_products' => 'one-to-many',]
现在要求做出一个页面以列表形式显示每个店铺，每个店铺块包含店铺信息如标题、包含店铺商家信息如姓名和电话、包含拥有的商品信息如介绍和价格。看看有没有预加载会有什么不同。
开发环境：laravel5.1+mamp+php7+mysql5.5
先写个店铺列表页1.先装上开发插件三件套(具体可参考：laravel学习笔记之seeder填充数据小技巧)
不管咋样，先装上开发插件三件套：
composer require barryvdh/laravel-debugbar --devcomposer require barryvdh/laravel-ide-helper --devcomposer require mpociot/laravel-test-factory-helper --dev//config/app.php/** *develop plugin */ barryvdh\debugbar\serviceprovider::class,mpociot\laraveltestfactoryhelper\testfactoryhelperserviceprovider::class,barryvdh\laravelidehelper\idehelperserviceprovider::class,
2.写上表字段、表关联和测试数据填充器seeder
依次输入指令：
php artisan make:model merchant -mphp artisan make:model phone -mphp artisan make:model shop -mphp artisan make:model product -m
写上表字段和表关联：
class createmerchantstable extends migration{ /** * run the migrations. * * @return void */ public function up() { schema::create('merchants', function (blueprint $table) { $table->increments('id'); $table->string('username')->unique(); $table->string('email')->unique(); $table->string('first_name'); $table->string('last_name'); $table->timestamps(); }); } /** * reverse the migrations. * * @return void */ public function down() { schema::drop('merchants'); }}class createphonestable extends migration{ /** * run the migrations. * * @return void */ public function up() { schema::create('phones', function (blueprint $table) { $table->increments('id'); $table->integer('number')->unsigned(); $table->integer('merchant_id')->unsigned(); $table->timestamps(); $table->foreign('merchant_id') ->references('id') ->on('merchants') ->onupdate('cascade') ->ondelete('cascade'); }); } /** * reverse the migrations. * * @return void */ public function down() { schema::table('phones', function($table){ $table->dropforeign('merchant_id'); // drop foreign key 'user_id' from 'posts' table }); schema::drop('phones'); }}class createshopstable extends migration{ /** * run the migrations. * * @return void */ public function up() { schema::create('shops', function (blueprint $table) { $table->increments('id'); $table->string('name'); $table->string('slug')->unique(); $table->string('site'); $table->integer('merchant_id')->unsigned(); $table->timestamps(); $table->foreign('merchant_id') ->references('id') ->on('merchants') ->onupdate('cascade') ->ondelete('cascade'); }); } /** * reverse the migrations. * * @return void */ public function down() { schema::table('shops', function($table){ $table->dropforeign('merchant_id'); // drop foreign key 'user_id' from 'posts' table }); schema::drop('shops'); }}class createproductstable extends migration{ /** * run the migrations. * * @return void */ public function up() { schema::create('products', function (blueprint $table) { $table->increments('id'); $table->string('name'); $table->text('short_desc'); $table->text('long_desc'); $table->double('price'); $table->integer('shop_id')->unsigned(); $table->timestamps(); $table->foreign('shop_id') ->references('id') ->on('shops') ->onupdate('cascade') ->ondelete('cascade'); }); } /** * reverse the migrations. * * @return void */ public function down() { schema::table('products', function($table){ $table->dropforeign('shop_id'); // drop foreign key 'user_id' from 'posts' table }); schema::drop('products'); }}/** * app\merchant * * @property integer $id * @property string $username * @property string $email * @property string $first_name * @property string $last_name * @property \carbon\carbon $created_at * @property \carbon\carbon $updated_at * @property-read \app\phone $phone * @property-read \illuminate\database\eloquent\collection|\app\shop[] $shops * @method static \illuminate\database\query\builder|\app\merchant whereid($value) * @method static \illuminate\database\query\builder|\app\merchant whereusername($value) * @method static \illuminate\database\query\builder|\app\merchant whereemail($value) * @method static \illuminate\database\query\builder|\app\merchant wherefirstname($value) * @method static \illuminate\database\query\builder|\app\merchant wherelastname($value) * @method static \illuminate\database\query\builder|\app\merchant wherecreatedat($value) * @method static \illuminate\database\query\builder|\app\merchant whereupdatedat($value) * @mixin \eloquent */class merchant extends model{ /** * @return \illuminate\database\eloquent\relations\hasone */ public function phone() { return $this->hasone(phone::class, 'merchant_id'); } /** * @return \illuminate\database\eloquent\relations\hasmany */ public function shops() { return $this->hasmany(shop::class, 'merchant_id'); }}/** * app\phone * * @property integer $id * @property integer $number * @property integer $merchant_id * @property \carbon\carbon $created_at * @property \carbon\carbon $updated_at * @property-read \app\merchant $merchant * @method static \illuminate\database\query\builder|\app\phone whereid($value) * @method static \illuminate\database\query\builder|\app\phone wherenumber($value) * @method static \illuminate\database\query\builder|\app\phone wheremerchantid($value) * @method static \illuminate\database\query\builder|\app\phone wherecreatedat($value) * @method static \illuminate\database\query\builder|\app\phone whereupdatedat($value) * @mixin \eloquent */class phone extends model{ /** * @return \illuminate\database\eloquent\relations\belongsto */ public function merchant() { return $this->belongsto(merchant::class, 'merchant_id'); }}/** * app\product * * @property integer $id * @property string $name * @property string $short_desc * @property string $long_desc * @property float $price * @property integer $shop_id * @property \carbon\carbon $created_at * @property \carbon\carbon $updated_at * @property-read \illuminate\database\eloquent\collection|\app\shop[] $shop * @method static \illuminate\database\query\builder|\app\product whereid($value) * @method static \illuminate\database\query\builder|\app\product wherename($value) * @method static \illuminate\database\query\builder|\app\product whereshortdesc($value) * @method static \illuminate\database\query\builder|\app\product wherelongdesc($value) * @method static \illuminate\database\query\builder|\app\product whereprice($value) * @method static \illuminate\database\query\builder|\app\product whereshopid($value) * @method static \illuminate\database\query\builder|\app\product wherecreatedat($value) * @method static \illuminate\database\query\builder|\app\product whereupdatedat($value) * @mixin \eloquent */class product extends model{ /** * @return \illuminate\database\eloquent\relations\belongsto */ public function shop() { return $this->belongsto(shop::class, 'shop_id'); }}/** * app\shop * * @property integer $id * @property string $name * @property string $slug * @property string $site * @property integer $merchant_id * @property \carbon\carbon $created_at * @property \carbon\carbon $updated_at * @property-read \illuminate\database\eloquent\collection|\app\merchant[] $merchant * @property-read \illuminate\database\eloquent\collection|\app\product[] $products * @method static \illuminate\database\query\builder|\app\shop whereid($value) * @method static \illuminate\database\query\builder|\app\shop wherename($value) * @method static \illuminate\database\query\builder|\app\shop whereslug($value) * @method static \illuminate\database\query\builder|\app\shop wheresite($value) * @method static \illuminate\database\query\builder|\app\shop wheremerchantid($value) * @method static \illuminate\database\query\builder|\app\shop wherecreatedat($value) * @method static \illuminate\database\query\builder|\app\shop whereupdatedat($value) * @mixin \eloquent */class shop extends model{ /** * @return \illuminate\database\eloquent\relations\belongsto */ public function merchant() { return $this->belongsto(merchant::class, 'merchant_id'); } /** * @return \illuminate\database\eloquent\relations\hasmany */ public function products() { return $this->hasmany(product::class, 'shop_id'); }}
别忘了利用下开发三件套输入指令：
php artisan ide-helper:generatephp artisan ide-helper:modelsphp artisan test-factory-helper:generate
表的关系如图：
然后写seeder，可以参考laravel学习笔记之seeder填充数据小技巧:
php artisan make:seeder merchanttableseederphp artisan make:seeder phonetableseederphp artisan make:seeder shoptableseederphp artisan make:seeder producttableseederclass merchanttableseeder extends seeder{ /** * run the database seeds. * * @return void */ public function run() { $faker = faker\factory::create(); $datas = []; foreach (range(1, 20) as $key => $value) { $datas[] = [ 'username' => $faker->username , 'email' => $faker->safeemail , 'first_name' => $faker->firstname , 'last_name' => $faker->lastname , 'created_at' => \carbon\carbon::now()->todatetimestring(), 'updated_at' => \carbon\carbon::now()->todatetimestring() ]; } db::table('merchants')->insert($datas); }}class phonetableseeder extends seeder{ /** * run the database seeds. * * @return void */ public function run() { $faker = faker\factory::create(); $merchant_ids = \app\merchant::lists('id')->toarray(); $datas = []; foreach (range(1, 20) as $key => $value) { $datas[] = [ 'number' => $faker->randomnumber() , 'merchant_id' => $faker->randomelement($merchant_ids) , 'created_at' => \carbon\carbon::now()->todatetimestring(), 'updated_at' => \carbon\carbon::now()->todatetimestring() ]; } db::table('phones')->insert($datas); }}class shoptableseeder extends seeder{ /** * run the database seeds. * * @return void */ public function run() { $faker = faker\factory::create(); $merchant_ids = \app\merchant::lists('id')->toarray(); $datas = []; foreach (range(1, 40) as $key => $value) { $datas[] = [ 'name' => $faker->name , 'slug' => $faker->slug , 'site' => $faker->word , 'merchant_id' => $faker->randomelement($merchant_ids) , 'created_at' => \carbon\carbon::now()->todatetimestring(), 'updated_at' => \carbon\carbon::now()->todatetimestring() ]; } db::table('shops')->insert($datas); }}class producttableseeder extends seeder{ /** * run the database seeds. * * @return void */ public function run() { $faker = faker\factory::create(); $shop_ids = \app\shop::lists('id')->toarray(); $datas = []; foreach (range(1, 30) as $key => $value) { $datas[] = [ 'name' => $faker->name , 'short_desc' => $faker->text , 'long_desc' => $faker->text , 'price' => $faker->randomfloat() , 'shop_id' => $faker->randomelement($shop_ids) , 'created_at' => \carbon\carbon::now()->todatetimestring() , 'updated_at' => \carbon\carbon::now()->todatetimestring() ]; } db::table('products')->insert($datas); }}php artisan db:seed
3.写个简单view视图
(1)用repository pattern来组织代码
//app/repositorynamespace app\repository;interface shoprepositoryinterface{ public function all();}//app/repository/eloquentnamespace app\repository\eloquent;use app\repository\shoprepositoryinterface;use app\shop;class shoprepository implements shoprepositoryinterface{ /** * @var shop */ public $shop; public function __construct(shop $shop) { $this->shop = $shop; } public function all() { // todo: implement all() method. $shops = $this->shop->all(); return $shops; }}//app/provider/shoprepositoryserviceprovider//php artisan make:provider shoprepositoryserviceprovider/** * register the application services. * * @return void */ public function register() { $this->app->bind(shoprepositoryinterface::class, shoprepository::class); } //app/http/controllers/shopcontroller.phpclass shopcontroller extends controller{ /** * @var shoprepositoryinterface */ public $shop; /** * shopcontroller constructor. * @param shoprepositoryinterface $shoprepositoryinterface */ public function __construct(shoprepositoryinterface $shoprepositoryinterface) { $this->shop = $shoprepositoryinterface; } public function all() { $shops = $this->shop->all(); return view('shop.index', compact('shops')); }}//视图//resources/views/shop/layout.blade.php<html lang="en"><head> <meta charset="utf-8"> <meta http-equiv="x-ua-compatible" content="ie=edge"> <meta name="viewport" content="width=device-width, initial-scale=1"> <!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ --> <title>bootstrap template</title> <!-- 新 bootstrap 核心 css 文件 --> <link rel="stylesheet" href="//cdn.bootcss.com/bootstrap/3.3.5/css/bootstrap.min.css"> <style> html,body{ width: 100%; height: 100%; } *{ margin: 0; border: 0; } </style></head><body><p class="container"> <p class="row"> <p class="col-xs-12 col-md-12"> @yield('content') </p> </p></p><!-- jquery文件。务必在bootstrap.min.js 之前引入 --><script src="//cdn.bootcss.com/jquery/1.11.3/jquery.min.js"></script><!-- 最新的 bootstrap 核心 javascript 文件 --><script src="//cdn.bootcss.com/bootstrap/3.3.5/js/bootstrap.min.js"></script><script></script></body></html>//resources/views/shop/index.blade.php@extends('shop.layout')@section('content') <ul class="list-group"> @foreach($shops as $shop) <li class="list-group-item" style="margin-top: 10px"> <h1><strong style="color: darkred">store:</strong>{{$shop->name}}</h1> <span><strong style="color: orangered">member:</strong>{{$shop->merchant->first_name.' '.$shop->merchant->last_name}}</span> {{--这里数组取电话号码--}} <span><strong style="color: orangered">phone:</strong>{{$shop->merchant->phone['number']}}</span> <ul class="list-group"> @foreach($shop->products as $product) <li class="list-group-item"> <h3><strong style="color: red">name:</strong>{{$product->name}}</h3> <h4><strong style="color: red">desc:</strong>{{$product->short_desc}}</h4> <h4><strong style="color: red">price:</strong>{{$product->price}}</h4>{{-- {!! debugbar::info('products:'.$product->id) !!}--}} </li> @endforeach </ul> </li> @endforeach </ul>@endsection//路由route::get('/eagerload', 'shopcontroller@all');
(2)debugbar查看程序执行数据
可以看到，执行了121次query，耗时38.89ms，效率很低，仔细观察每一个statement就发现这是先扫描shops表，再根据shops中每一个merchant_id去查找merchants表，查找products表也是这样，又有很多次query，这是n+1查找问题。
预加载查询(1)嵌套预加载
eloquent在通过属性访问关联数据时是延迟加载的，就是只有该关联数据只有在通过属性访问它时才会被加载。在查找上层模型时可以通过预加载关联数据，避免n+1问题。而且，使用预加载超级简单。
只需修改一行：
//app/repository/eloquent/shoprepository public function all() { // todo: implement all() method.// $shops = $this->shop->all(); //通过`点`语法嵌套预加载，多种关联就写对应的关联方法 //shop这个model里关联方法是merchant()和products()，merchant model里关联方法是phone() $shops = $this->shop->with(['merchant.phone', 'products'])->get(); return $shops; }
不需要修改其他代码，再看debugbar里的查询：
it is working!!!
发现：只有4个query，耗时3.58ms，效率提高很多。把原来的n+1这种query改造成了where..in..这种query，效率提高不少。可以用explain来查看sql语句的执行计划。
(2)预加载条件限制
还可以对预加载进行条件限制，如对products进行预先排序，代码也很好修改，只需：
//app/repository/eloquent/shoprepositorypublic function all() { // todo: implement all() method.// $shops = $this->shop->all();// $shops = $this->shop->with(['merchant.phone', 'products'])->get(); $shops = $this->shop->with(['members.phone', 'products'=>function($query){// $query->orderby('price', 'desc'); $query->orderby('price', 'asc'); }])->get(); return $shops; }
通过加个限制条件，就等于在预加载products时sql语句上加个排序。截图就不截取了。
总结：关联模型预加载的确是个有意思的功能，效率提高不少。最近都在瞎研究，遇到好玩的东西再分享出来吧，到时见。
以上就是详解laravel之模型关联预加载的详细内容。