Detailed pre-loading optimization Laravel model query related issues

This article mainly introduces how to use the Pre-loading optimization laravel model query information, the article through the sample code introduced in very detailed, to everyone's study or work has a certain reference learning value, the need for friends to follow the small series to learn together.

Objective

This article mainly introduces the use of pre-loading optimization laravel model query related content, share out for everyone to reference the study, words not to say, come together to see the detailed introduction:

Introduced

Object Relational Mapping (ORM) makes the work of a database very simple. When you define database relationships in an object-oriented manner, you can easily query related model data, and developers may not be aware of underlying database calls.

Here are some examples to help you understand how to optimize your queries.

Suppose you received 100 objects from a database, and each record has 1 association models (that is, Belongsto). Using ORM by default will generate 101 queries; As shown below:

Get published 100 articles $posts = Post::limit (+)->get (); One query $authors = array_map (function ($post) {//Generate query to author model return $post->author->name;}, $posts);

We did not tell the post model at the time of the query, we also needed all the authors, so a separate query would occur each time the author's name was obtained from a single post model instance.

Array_maps occurs 100 times, plus the previous query, the cumulative 101 queries.

Pre-load

Next, if we are going to use the associated model data, we can use preloading to reduce the total number of 101 queries to 2 queries. You just need to tell the model what you need to load. As follows:

Get published 100 articles-and preload articles corresponding to author $posts = Post::with (' author ')->limit (+)->get ();//2 Queries $authors = Array_map ( function ($post) {//Generate query for author model return $post->author->name;//here does not generate query}, $posts);

If you turn on the SQL log, you will see that the pre-load above will only produce two queries:

SELECT * from ' posts ' select * from ' authors ' where ' authors '. ' ID ' in (?,?,?,?,?) [1,2,3,4,5]

If you have more than one association model, you can use an array to load them:

$posts = App\post::with ([' Author ', ' comments '])->get ();

Next we redefine the following relationship

Belongsto, post, Author/per article only one user Author-hasmany-post/per-user with multiple articles Author-HasOne Le//each user has only one profile

Consider the following scenario: Get a profile of the author who published the article.

Get all articles-and preload the article corresponding to author $posts = App\post::with (' author ')->get ();//two queries//per ' author ' for its introduction $posts->map (function ($ Post) {//Although we directly pass $author = $post->author does not produce a query,//But when $author->profile is called, a new query is generated each time the return $post->author- >profile;});

Assuming App\Post::with('author')->get() that there are 100 records above, how many queries will be generated?

By optimizing preloading, we can avoid additional queries in nested relationships.

Get all articles-and preload the article corresponding to the author and each author corresponding to de profile$posts = App\post::with (' author.profile ')->get ();//Three Queries $posts->map ( function ($post) {//does not produce a new query return $post->author->profile;});

You can open your SQL log to see the corresponding three queries.

SELECT * from ' posts ' select * from ' authors ' where ' authors '. ' ID ' in (?,?,?,?,?) [.....] SELECT * from ' Profiles ' where ' profiles '. ' author_id ' in (?,?,?,?,?) [.....]

Lazy Loading

Sometimes you may only need to collect the associated model based on conditions. In this case, you can lazily invoke other queries related to the data:

$posts = App\post::all ();//Query $posts->load (' author.profile ');//two Queries $posts->map (function ($post) {//does not generate new query return $post->author->profile;});

See your SQL logs for a total of three queries, but only if you call $posts->load ().

Conclusion

Hopefully, you'll learn more about loading models and learn how they work in a deeper sense. Laravel related documentation is comprehensive and hopefully additional hands-on exercises will help you to optimize your relational queries with confidence.

Summarize

The original text is translated from eloquent-eager-loading, simplifying its previous construction data section.