This post is from the professional Laravel developer community, original link: learnku.com/laravel/t/1…

Object relational mapping (ORM) makes working with data surprisingly easy. Because defining relationships between data in an object-oriented manner makes it easy to query relational model data, developers need to focus less on the underlying data calls.

The standard data optimization for ORM is to eagerly load relevant data. We’ll set up some sample relationships and then walk through how queries vary with desired and undesired loads. I like to experiment with a few things directly with the code and walk through some examples of how eager loading works, which will further help you understand how to optimize queries.

introduce

At the basic level, ORM is “lazy” to load relevant model data. But how should ORM know your intentions? After querying the model, you may never actually use the data from the relevant model. Not optimizing queries is known as the “N + 1” problem. When you use objects to represent queries, you may be making queries without knowing it.

Imagine that you receive 100 objects from a database and each record has a model (belongsTo) associated with it. Using ORM produces 101 queries by default; One query is made for the original 100 records, and additional queries are made for each record if relevant data on the model object is accessed. In the pseudocode, suppose you want to list the authors of all published posts. From a set of posts (each with an author), you can get a list of author names, as follows:

$posts= Post::published()->get(); // a query$authors = array_map(function($post) {// Generate a query against the author's modelreturn $post->author->name;
}, $posts);

Copy the code

We do not tell the model that we need all authors, so a separate query occurs each time the author name is retrieved from each Post model instance.

preload

As I mentioned, ORM is “lazy” to load associations. If you plan to use associated model data, you can use preloading to reduce 101 queries to 2. You just need to tell the model what you want it to load.

Here is an example using the pre-loaded Rails Active Record Guide. As you can see, this concept is very similar to Laravel’s eager Loading concept.

# Rails
posts = Post.includes(:author).limit(100)

# Laravel
$posts = Post::with('author') - >limit(100)->get();

Copy the code

By exploring from a broader perspective, I found that I gained a better understanding. The Active Record document covers examples that further help the idea resonate.

Laravel 的 Eloquent ORM

Laravel’s ORM, also known as Eloquent, makes it easy to preload models, even nested relationships. Let’s use the Post model as an example to learn how to use preloading in a Laravel project. We’ll build with this project, then take a closer look at some preloading examples to conclude.

build

Let’s build some database migrations, models, and database seeds to experience preloading. If you want to follow along, I’m assuming you have access to the database and can complete the basic Laravel installation.

Using the Laravel installer, create a new project:

laravel new blog-example

Copy the code

Edit the.env file according to your database and choose.

Next, we will create three models so that you can try to preload nested relationships. The example is simple, so we can focus on preloading, and I’ve omitted things you might use, such as indexes and foreign key constraints.

php artisan make:model -m Post
php artisan make:model -m Author
php artisan make:model -m Profile

Copy the code

The -m flag creates a migration to be used with the model that will be used to create the table schema.

The data model will have the following associations:

Post -> belongsTo -> Author
Author -> hasMany -> Post
Author -> hasOne -> Profile

Copy the code

The migration

Let’s create a simple table structure for each data table. I just added the up() method because Laravel will automatically add the Down () method for new tables. These migration files are placed in the database/migrations/ directory:

<? php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; Class CreatePostsTable extends Migration {/** * Performs Migration ** @return void
     */
    public function up()
    {
        Schema::create('posts'.function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('author_id');
            $table->string('title');
            $table->text('body');
            $table->timestamps(); }); } /** * rollback migration ** @return void
     */
    public function down()
    {
        Schema::dropIfExists('posts'); }}Copy the code
<? php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; Class CreateAuthorsTable extends Migration {/** * Performs Migration ** @return void
     */
    public function up()
    {
        Schema::create('authors'.function (Blueprint $table) {
            $table->increments('id');
            $table->string('name');
            $table->text('bio');
            $table->timestamps(); }); } /** * rollback migration ** @return void
     */
    public function down()
    {
        Schema::dropIfExists('authors'); }}Copy the code
<? php use Illuminate\Support\Facades\Schema; use Illuminate\Database\Schema\Blueprint; use Illuminate\Database\Migrations\Migration; Class CreateProfilesTable extends Migration {/** * Performs Migration ** @return void
     */
    public function up()
    {
        Schema::create('profiles'.function (Blueprint $table) {
            $table->increments('id');
            $table->unsignedInteger('author_id');
            $table->date('birthday');
            $table->string('city');
            $table->string('state');
            $table->string('website');
            $table->timestamps(); }); } /** * rollback migration ** @return void
     */
    public function down()
    {
        Schema::dropIfExists('profiles'); }}Copy the code

model

You need to define model associations and do more experiments by preloading. When you run the PHP artisan make:model command, it will create the model file for you.

The first model is app/ post.php:

<? php namespace App; use Illuminate\Database\Eloquent\Model; class Post extends Model { publicfunction author()
    {
        return $this->belongsTo(Author::class); }}Copy the code

Next, the app\ author.php model has two relationships:

<? php namespace App; use Illuminate\Database\Eloquent\Model; class Author extends Model { publicfunction profile()
    {
        return $this->hasOne(Profile::class);
    }

    public function posts()
    {
        return $this->hasMany(Post::class); }}Copy the code

With models and migrations, you can run migrations and continue trying to preload with some seed model data.

php artisan migrate
Migration table created successfully.
Migrating: 2014_10_12_000000_create_users_table
Migrated:  2014_10_12_000000_create_users_table
Migrating: 2014_10_12_100000_create_password_resets_table
Migrated:  2014_10_12_100000_create_password_resets_table
Migrating: 2017_08_04_042509_create_posts_table
Migrated:  2017_08_04_042509_create_posts_table
Migrating: 2017_08_04_042516_create_authors_table
Migrated:  2017_08_04_042516_create_authors_table
Migrating: 2017_08_04_044554_create_profiles_table
Migrated:  2017_08_04_044554_create_profiles_table

Copy the code

If you look in the database, you’ll see all the tables that have been created!

The factory model

In order for us to run the query, we need to create some dummy data to serve the query. Let’s add some factory models and use these models to serve the test data to the database.

Open the database/factories/ModelFactory. PHP file and add the following three factory model to the existing User factory model file:

/** @var \Illuminate\Database\Eloquent\Factory $factory* /$factory->define(App\Post::class, function (Faker\Generator $faker) {
    return [
        'title'= >$faker->sentence,
        'author_id'= >function () {
            return factory(App\Author::class)->create()->id;
        },
        'body'= >$faker- > paragraphs (rand (3, 10),true)]; }); /** @var \Illuminate\Database\Eloquent\Factory$factory* /$factory->define(App\Author::class, function (Faker\Generator $faker) {
    return [
        'name'= >$faker->name,
        'bio'= >$faker->paragraph,
    ];
});

$factory->define(App\Profile::class, function (Faker\Generator $faker) {
    return [
        'birthday'= >$faker->dateTimeBetween('-100 years'.'-18 years'),
        'author_id'= >function () {
            return factory(App\Author::class)->create()->id;
        },
        'city'= >$faker->city,
        'state'= >$faker->state,
        'website'= >$faker->domainName,
    ];
});

Copy the code

These factory models can be easily populated with data that we can query; We can also use them to create and generate the data needed for the relational model.

Open the database/seeds/DatabaseSeeder. PHP file add the following to the DatabaseSeeder: : run () method:

public function run()
{
    $authors = factory(App\Author::class, 5)->create();
    $authors->each(function ($author) {
        $author
            ->profile()
            ->save(factory(App\Profile::class)->make());
        $author- > posts () - > saveMany (factory (App \ Post: : class, rand (20, 30)) - > make ()); }); }Copy the code

You create five authors and loop through each one, creating and saving profiles and posts associated with each author (the number of posts per author is between 20 and 30).

We have completed the creation of the migration, model, factory model, and database population, which together can rerun the migration and database population in a repetitive manner:

php artisan migrate:refresh
php artisan db:seed

Copy the code

You should now have some populated data that you can use in the next section. Note that Laravel 5.5 includes a Migrate :fresh command, which deletes tables rather than rolling back the migration and reapplying them.

Try using preloading

Now our preliminary work has finally been completed. In my opinion, the best way to visualize the query results is to view them in storage/logs/laravel.log.

There are two ways to log query results. The first is to start the MySQL log file. The second is to use a Eloquent database call. By Eloquent record query, you can add the following code to the app/will/AppServiceProvider PHP boot () method:

namespace App\Providers;

use DB;
use Log;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::listen(function($query) {
            Log::info(
                $query->sql,
                $query->bindings,
                $query->time
            );
        });
    }

    // ...
}

Copy the code

I like to wrap this listener in configuration checks so that I can control the query logging switch. You can also get more information from the Laravel Debugbar.

First, try what happens when you don’t use a preloaded model. Clear the contents of your storage/log/laravel.log file and run “tinker” :

php artisan tinker

>>> $posts = App\Post::all();
>>> $posts->map(function ($post) {...return $post->author; . }); > > >...Copy the code

Check your laravel.log file at this point and you’ll find a bunch of queries for authors:

[2017-08-04 06:21:58] local.INFO: select * from `posts`
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit 1 [1]
[2017-08-04 06:22:06] local.INFO: select * from `authors` where `authors`.`id` = ? limit1 [1]...Copy the code

Then, empty the laravel.log file again, this time using the with() method to query author information with preloading:

php artisan tinker

>>> $posts = App\Post::with('author')->get();
>>> $posts->map(function ($post) {...return $post->author; . }); .Copy the code

This time you should see that there are only two queries. One is to query all posts, as well as the authors associated with the post:

[2017-08-04 07:18:02] local.INFO: select * from `posts`
[2017-08-04 07:18:02] local.INFO: select * from `authors` where `authors`.`id` in(? ,? ,? ,? ,?) [1, 2, 3, 4, 5]Copy the code

If you have multiple associated models, you can use an array for preloading implementations:

$posts = App\Post::with(['author'.'comments'])->get();

Copy the code

Nested preloading in Eloquent

Nested preloading to do the same job. In our example, each author’s model has an associated profile. Therefore, we will query for each profile.

Empty the laravel.log file to try:

php artisan tinker

>>> $posts = App\Post::with('author')->get();
>>> $posts->map(function ($post) {...return $post->author->profile; . }); .Copy the code

You can now see seven queries, the first two of which are preloaded results. Then, each time we get a new profile, we need to query all author profiles.

By preloading, we can avoid additional queries nested in model associations. Empty the laravel.log file one last time and run the following command:

>>> $posts = App\Post::with('author.profile')->get();
>>> $posts->map(function ($post) {...return $post->author->profile; . });Copy the code

There are now three query statements in total:

[2017-08-04 07:27:27] local.INFO: select * from `posts`
[2017-08-04 07:27:27] local.INFO: select * from `authors` where `authors`.`id` in(? ,? ,? ,? ,?) [1,2,3,4,5] [2017-08-04 07:27:27] local.info: select * from 'profiles'where `profiles`.`author_id` in(? ,? ,? ,? ,?) [1, 2, 3, 4, 5]Copy the code

Lazy preloading

You may only need to gather some basic conditions for the relational model. In this case, you can lazily invoke some other query with associated data:

php artisan tinker

>>> $posts= App\Post::all(); . >>>$posts->load('author.profile');
>>> $posts->first()->author->profile; .Copy the code

You should only see three queries, and that’s after calling the $posts-> Load () method.

conclusion

Hopefully you’ll learn more about the preload model and how it works at a deeper level. The preloaded documentation is quite comprehensive, and I hope that some additional code implementations will help you better optimize the associated queries.