• 技术文章 >php框架 >Laravel

    详解Laravel之模型关联预加载

    藏色散人藏色散人2021-04-12 08:58:36转载157
    下面由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
     */        
    Barryvdh\Debugbar\ServiceProvider::class,
    Mpociot\LaravelTestFactoryHelper\TestFactoryHelperServiceProvider::class,
    Barryvdh\LaravelIdeHelper\IdeHelperServiceProvider::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');
        }
    }
    
    /**
     * 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:generate
    php artisan ide-helper:models
    php artisan test-factory-helper:generate

    表的关系如图:

    df49f7bcdf0cecf399a0f9daa27dd12.png

    然后写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 = 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/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
    <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查看程序执行数据
    d777dc41ca87ce95aed7da74438ba3c.png

    可以看到,执行了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里的查询:
    af843e3594f856dc5e66997f968e425.png

    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语句上加个排序。截图就不截取了。

    总结:关联模型预加载的确是个有意思的功能,效率提高不少。最近都在瞎研究,遇到好玩的东西再分享出来吧,到时见。

    以上就是详解Laravel之模型关联预加载的详细内容,更多请关注php中文网其它相关文章!

    声明:本文转载于:segmentfault,如有侵犯,请联系admin@php.cn删除
    专题推荐:laravel
    上一篇:laravel查询不再需要写大量if else判断了! 下一篇:关于laravel中的错误与日志
    第15期线上培训班

    相关文章推荐

    • Laravel中轻松输出完整的SQL语句• 详解Laravel路由之domain解决多域名问题• 50道面试官必问的Laravel面试题【附答案】• laravel查询不再需要写大量if else判断了!

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网