下面由laravel教程欄目給大家介紹laravel之模型關聯預加載,希望對需要的朋友有所幫助!
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 --dev composer require barryvdh/laravel-ide-helper --dev composer require mpociot/laravel-test-factory-helper --dev //config/app.php /** *Develop Plugin */ BarryvdhDebugbarServiceProvider::class, MpociotLaravelTestFactoryHelperTestFactoryHelperServiceProvider::class, BarryvdhLaravelIdeHelperIdeHelperServiceProvider::class,
2.寫上表字段、表關聯和測試數據填充器Seeder
依次輸入指令:
php artisan make:model Merchant -m php artisan make:model Phone -m php artisan make:model Shop -m php 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'); } } /** * AppMerchant * * @property integer $id * @property string $username * @property string $email * @property string $first_name * @property string $last_name * @property CarbonCarbon $created_at * @property CarbonCarbon $updated_at * @property-read AppPhone $phone * @property-read IlluminateDatabaseEloquentCollection|AppShop[] $shops * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereId($value) * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereUsername($value) * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereEmail($value) * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereFirstName($value) * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereLastName($value) * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereCreatedAt($value) * @method static IlluminateDatabaseQueryBuilder|AppMerchant whereUpdatedAt($value) * @mixin Eloquent */ class Merchant extends Model { /** * @return IlluminateDatabaseEloquentRelationsHasOne */ public function phone() { return $this->hasOne(Phone::class, 'merchant_id'); } /** * @return IlluminateDatabaseEloquentRelationsHasMany */ public function shops() { return $this->hasMany(Shop::class, 'merchant_id'); } } /** * AppPhone * * @property integer $id * @property integer $number * @property integer $merchant_id * @property CarbonCarbon $created_at * @property CarbonCarbon $updated_at * @property-read AppMerchant $merchant * @method static IlluminateDatabaseQueryBuilder|AppPhone whereId($value) * @method static IlluminateDatabaseQueryBuilder|AppPhone whereNumber($value) * @method static IlluminateDatabaseQueryBuilder|AppPhone whereMerchantId($value) * @method static IlluminateDatabaseQueryBuilder|AppPhone whereCreatedAt($value) * @method static IlluminateDatabaseQueryBuilder|AppPhone whereUpdatedAt($value) * @mixin Eloquent */ class Phone extends Model { /** * @return IlluminateDatabaseEloquentRelationsBelongsTo */ public function merchant() { return $this->belongsTo(Merchant::class, 'merchant_id'); } } /** * AppProduct * * @property integer $id * @property string $name * @property string $short_desc * @property string $long_desc * @property float $price * @property integer $shop_id * @property CarbonCarbon $created_at * @property CarbonCarbon $updated_at * @property-read IlluminateDatabaseEloquentCollection|AppShop[] $shop * @method static IlluminateDatabaseQueryBuilder|AppProduct whereId($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct whereName($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct whereShortDesc($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct whereLongDesc($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct wherePrice($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct whereShopId($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct whereCreatedAt($value) * @method static IlluminateDatabaseQueryBuilder|AppProduct whereUpdatedAt($value) * @mixin Eloquent */ class Product extends Model { /** * @return IlluminateDatabaseEloquentRelationsBelongsTo */ public function shop() { return $this->belongsTo(Shop::class, 'shop_id'); } } /** * AppShop * * @property integer $id * @property string $name * @property string $slug * @property string $site * @property integer $merchant_id * @property CarbonCarbon $created_at * @property CarbonCarbon $updated_at * @property-read IlluminateDatabaseEloquentCollection|AppMerchant[] $merchant * @property-read IlluminateDatabaseEloquentCollection|AppProduct[] $products * @method static IlluminateDatabaseQueryBuilder|AppShop whereId($value) * @method static IlluminateDatabaseQueryBuilder|AppShop whereName($value) * @method static IlluminateDatabaseQueryBuilder|AppShop whereSlug($value) * @method static IlluminateDatabaseQueryBuilder|AppShop whereSite($value) * @method static IlluminateDatabaseQueryBuilder|AppShop whereMerchantId($value) * @method static IlluminateDatabaseQueryBuilder|AppShop whereCreatedAt($value) * @method static IlluminateDatabaseQueryBuilder|AppShop whereUpdatedAt($value) * @mixin Eloquent */ class Shop extends Model { /** * @return IlluminateDatabaseEloquentRelationsBelongsTo */ public function merchant() { return $this->belongsTo(Merchant::class, 'merchant_id'); } /** * @return IlluminateDatabaseEloquentRelationsHasMany */ public function products() { return $this->hasMany(Product::class, 'shop_id'); } }
別忘了利用下開發三件套輸入指令:
php artisan ide-helper:generate php artisan ide-helper:models php artisan test-factory-helper:generate
表的關系如圖:
然后寫Seeder,可以參考Laravel學習筆記之Seeder填充數據小技巧:
php artisan make:seeder MerchantTableSeeder php artisan make:seeder PhoneTableSeeder php artisan make:seeder ShopTableSeeder php artisan make:seeder ProductTableSeeder class MerchantTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $faker = FakerFactory::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' => CarbonCarbon::now()->toDateTimeString(), 'updated_at' => CarbonCarbon::now()->toDateTimeString() ]; } DB::table('merchants')->insert($datas); } } class PhoneTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $faker = FakerFactory::create(); $merchant_ids = AppMerchant::lists('id')->toArray(); $datas = []; foreach (range(1, 20) as $key => $value) { $datas[] = [ 'number' => $faker->randomNumber() , 'merchant_id' => $faker->randomElement($merchant_ids) , 'created_at' => CarbonCarbon::now()->toDateTimeString(), 'updated_at' => CarbonCarbon::now()->toDateTimeString() ]; } DB::table('phones')->insert($datas); } } class ShopTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $faker = FakerFactory::create(); $merchant_ids = AppMerchant::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' => CarbonCarbon::now()->toDateTimeString(), 'updated_at' => CarbonCarbon::now()->toDateTimeString() ]; } DB::table('shops')->insert($datas); } } class ProductTableSeeder extends Seeder { /** * Run the database seeds. * * @return void */ public function run() { $faker = FakerFactory::create(); $shop_ids = AppShop::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' => CarbonCarbon::now()->toDateTimeString() , 'updated_at' => CarbonCarbon::now()->toDateTimeString() ]; } DB::table('products')->insert($datas); } } php artisan db:seed
3.寫個簡單View視圖
(1)用Repository Pattern來組織代碼
//app/Repository namespace AppRepository; interface ShopRepositoryInterface { public function all(); } //app/Repository/Eloquent namespace AppRepositoryEloquent; use AppRepositoryShopRepositoryInterface; use AppShop; 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.php class 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/ShopRepository public 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語句上加個排序。截圖就不截取了。
總結:關聯模型預加載的確是個有意思的功能,效率提高不少。最近都在瞎研究,遇到好玩的東西再分享出來吧,到時見。