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:seed3.写个简单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 Bootstrap Template html,body{ width: 100%; height: 100%; } *{ margin: 0; border: 0; } @yield('content')//resources/views/shop/index.blade.php@extends('shop.layout')@section('content') @foreach($shops as $shop) # **Store:**{{$shop->name}} **Member:**{{$shop->merchant->first_name.' '.$shop->merchant->last_name}} {{--这里数组取电话号码--}} **Phone:**{{$shop->merchant->phone['number']}} @foreach($shop->products as $product) ### **Name:**{{$product->name}} #### **Desc:**{{$product->short_desc}} #### **Price:**{{$product->price}}{{-- {!! Debugbar::info('products:'.$product->id) !!}--}} @endforeach @endforeach@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语句上加个排序。截图就不截取了。
昨天看到SegmentFault上有个问题是问Laravel值不值得花时间玩一玩，个人自学Laravel有几个月了，玩PHP也11个月，开始发现PHP的代码非常面条式，但后来了解到Laravel和Composer后，发现PHP原来很强大的，个人建议Laravel是值得花时间玩一玩的，也推荐一本书《Modern PHP》。其实写代码需要些很多东西的，而不仅仅面条式业务逻辑，比如用PHPUnit写单元测试和集成测试，各种设计模式，SOLID原则，依赖注入和控制反转的容器等等，这些东西个人都是玩Laravel后才了解并学习了一些，就个人而言，玩Laravel后学到了很多代码技巧，感觉到编程是一项技术活，不是体力活。
总结：关联模型预加载的确是个有意思的功能，效率提高不少。最近都在瞎研究，遇到好玩的东西再分享出来吧，到时见。
关键字：laravel, eloquent, loading
版权声明
本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处。如若内容有涉嫌抄袭侵权/违法违规/事实不符，请点击 举报 进行投诉反馈！
