Description: This article mainly explains Laravel Eloquent’s delayed preloading ( Eager Loading), uses lazy preloading to reduce the number of MySQL queries. At the same time, the author will paste some screenshots and codes during the development process to improve reading efficiency.
Note: There are now 4 tables: merchant table merchants, merchant phone table phones, merchant-owned shops shops table and products in the shop table products. And the relationship is:
[ 'merchants_phones' => 'one-to-one', 'merchants_shops' => 'one-to-many', 'shops_products' => 'one-to-many', ]
Now it is required to make a page to display each store in a list. Each store block contains store information such as title, store merchant information such as name and phone number, and owned product information such as Introduction and price. See what difference it makes with or without preloading.
Development environment: Laravel5.1 MAMP PHP7 MySQL5.5
Write a store list page first
1. Install development first Three-piece plug-in set (For details, please refer to: Laravel Learning Notes: Seeder Filling Data Tips)
No matter what, first install the three-piece development plug-in set:
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 */ Barryvdh\Debugbar\ServiceProvider::class, Mpociot\LaravelTestFactoryHelper\TestFactoryHelperServiceProvider::class, Barryvdh\LaravelIdeHelper\IdeHelperServiceProvider::class,
2. Write the table fields, table associations and test data filler Seeder
and enter the instructions in sequence:
php artisan make:model Merchant -m php artisan make:model Phone -m php artisan make:model Shop -m php artisan make:model Product -m
Write the table fields and table associations:
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');
}
}
Don’t forget In order to develop a three-piece set of input instructions:
php artisan ide-helper:generate php artisan ide-helper:models php artisan test-factory-helper:generate
The relationship between the tables is as shown in the figure:
Then write Seeder, you can refer to Laravel study notes Tips for filling data in 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 = 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. Write a simple View view(1) Use Repository Pattern to organize the code
//app/Repository
namespace App\Repository;
interface ShopRepositoryInterface
{
public function all();
}
//app/Repository/Eloquent
namespace 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.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
<meta>
<meta>
<meta>
<!-- 上述3个meta标签*必须*放在最前面，任何其他内容都*必须*跟随其后！ -->
<title>Bootstrap Template</title>
<!-- 新 Bootstrap 核心 CSS 文件 -->
<link>
<style>
html,body{
width: 100%;
height: 100%;
}
*{
margin: 0;
border: 0;
}
</style>
<p>
</p><p>
</p><p>
@yield('content')
</p>
<!-- jQuery文件。务必在bootstrap.min.js 之前引入 -->
<script></script>
<!-- 最新的 Bootstrap 核心 JavaScript 文件 -->
<script></script>
<script>
</script>
//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
-
(2) Debugbar View program execution data
You can see that 121 queries were executed, taking 38.89ms, and the efficiency is very low. Carefully observe each query One statement found that this is to first scan the shops table, and then search the merchants table according to each merchant_id in the shops. The same is true for searching the products table. There are many queries. This is an N 1 search problem.
Preloading query
(1) Nested preloading
Eloquent is lazy loading when accessing related data through attributes. That is, only the associated data will be loaded when accessing it through properties. You can avoid the N 1 problem by preloading related data when searching for upper-layer models. Also, using preloading is super easy.
You only need to modify one line:
//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;
}
No need to modify other codes, look at the query in the Debugbar: 
It is working! !!
Found: There are only 4 queries, which takes 3.58ms, and the efficiency is greatly improved. The original N 1 query was transformed into the where..in.. query, which greatly improved the efficiency. You can use EXPLAIN to view the execution plan of a SQL statement.
(2) Preloading condition restrictions
You can also impose conditional restrictions on preloading, such as pre-sorting products, and the code is easy to modify, just:
//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;
}
By adding a restriction condition, it is equivalent to adding a sorting to the SQL statement when preloading products. No more screenshots.
Summary: Associated model preloading is indeed an interesting function, and the efficiency is improved a lot. I've been doing some random research recently. If I come across something interesting, I'll share it. See you then.