详解Laravel之模型关联预加载

2022-05-15 0 202

下面由laravel教程栏目给大家介绍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 --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

表的关系如图:

详解Laravel之模型关联预加载

然后写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查看程序执行数据
详解Laravel之模型关联预加载

可以看到,执行了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里的查询:
详解Laravel之模型关联预加载

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

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

收藏 (0) 打赏

感谢您的支持,我会继续努力的!

打开微信/支付宝扫一扫,即可进行扫码打赏哦,分享从这里开始,精彩与您同在
点赞 (0)

【声明:根据2013年1月30日《计算机软件保护条例》2次修订第17条规定: 为了学习和研究软件内含的设计思想和原理,通过安装、显示、传输或者存 储软件等方式使用软件的,可以不经软件著作权人许可,不向其支付报酬! 鉴于此,也希望大家按此说明研究软件!】
本站所有源码尽量保证原汁原味,如有特殊情况会作出声明及标注,网站资源不做任何二次加密(原版加密除外,不影响程序使用的不会做解密处理),方便您更好的学习参考。 在您的能力范围内,为了大环境的良性发展,请尽可能的选择正版资源。如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。

即刻码站__国内靠谱的站长资源下载平台 php教程 详解Laravel之模型关联预加载 https://www.jike1995.com/36327.html

常见问题
  • 本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用
查看详情
  • 最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度
查看详情

相关文章

发表评论
暂无评论
官方客服团队

为您解决烦忧 - 24小时在线 专业服务