[EagerLoad] Which is more useful: leftJoin or with? [N+1 problem]

``If you've never written a raw query before, you better have a real sense of crisis,''
says Enoki, a member of the System Development Department, who has been frequently told by his seniors and superiors recently.
(By the way, he is now in charge of MySQL training for new graduates, so he feels a real sense of crisis.)

N+1 problem

(Leaving my story aside)
Let's assume that a relationship like this is set up.

class Bolg { public function comments(): HasMany { return $this->hasMany(Comment::class); } }

Get the Bolg model

$blogs= Bolg::query()->first();

Here the below query is issued

"select * from `blogs`"

If you loop further, you can get all the Comment models associated with each Blog model.

$comments= $blogs->map(function ($blog) { return $blogs->comments->first(); });

Naturally, since it is in a loop, the following query will be executed for as many chapters as there are chapters.

"select * from `blogs` where `blogs`.`blog_id` = ? and `blogs`.`blog_id` is not null"

One query to get all the first results + N queries are issued.
This is a 1+N problem.

The problem is that, of course, queries are issued for the number of chapters, so the problem is simply that the load is being increased.

join and with to avoid N+1

We have come to the main topic.

As mentioned above, issuing a query each time is extremely inefficient, so use (left)join or with.
(By the way, in some cases it may be better to retrieve it each time, so choose carefully.)
Each of these can be used to prevent a large number of queries from flowing.
However, they each do different things.

What is join?

join performs an inner join.
Get common data between tables and eliminate the rest.

leftJoin performs a Left Outer Join.
Returns all rows from the table on the left and any matching rows from the table on the right.
Retrieves data that matches the join condition while retaining all data in the left table.

What these joins are is a feature of SQL.
You are joining multiple tables within the query.
The query looks like this.

0 => array:3[ "query" => "select * from `blogs` inner join `comments` on `blogs`.`id` = `blog_id`" "bindings" => [] "time" => 1.5 ],

It only takes one query.
It feels like tremendous progress.

So what about with?

with

The with query is executed twice.
Although it loses to join, it is a tremendous improvement considering that the loop runs N+1 times.

By the way, the string used as an argument for with is the name of the relationship definition method in the model file.
(This is actually the same as the model name of the relation destination, but please be careful as with cannot be used unless it is defined as a method.)

$blogAndComments= Blog::query() ->with('comments') ->first();

Looking at the query, you can see that the data can be retrieved all at once thanks to whereIn.

0 => array:3[ "query" => "select * from `blogs`" "bindings" => [] "time" => 0.5 ], 1 => array:3[ "query" => "select * from `blogs` where `blogs`.`blogt_id` in (?)" "bindings" => [] "time" => 1.5 ],

Characteristics and usage of each

We acquired the model using two methods, but let's take a look at the characteristics of each.

● (left)
Join Combines models based on the join condition.
Join is a SQL feature used to retrieve data from multiple tables at once.
When retrieving, only the amount of data that matches the join conditions is created .
For example, you can think of the final data being created as many times as there are Comment models.
If this joins multiple tables, incorrect data may be created.

it is ideal when you do not need it for the model or

●
Obtain the with model in the form of an associative array without combining it.
It solves the N+1 problem by pre-loading the model relationships in ORM

For example, the Comment model associated with the Bolg model will exist in a property called relations.
Models can be nested and retrieved in hierarchical order of parent-child relationships, so models are not combined.
Retrieve related models and related data at once, without creating redundant data.
It is also useful when you want to treat the acquired data as a model as is.

By the way, with can be obtained as long as the relation continues, such as (ModelA.ModelB.ModelC.), but since it is nested, all the models that passed through it will be attached as outer shells, so in the end, you will need to unset, etc. You need to do it and eliminate it.

Summary and asides

Now that I have all the information, I will summarize it and conclude with a little digression.

summary

What do you think?
Since there was a lot of information, I decided to put it into a table.

I don't know which ones are really useful! ! !
Roughly speaking, the result is retrieving data from multiple tables, but what they are doing is very different.

So, this is a typical summary: "Please change the acquisition method according to the data type you want."
I think the easiest way to tell is
whether it is the same shape as the model or whether it can be combined Alternatively, whether the nested or flat shape is easier to use in future processing , you may be able to use it properly.

In addition, I think it should be
changed depending on the total amount of data you want to acquire Since with reads the relations, the deeper the nesting, the slower it is than join.
In other words, it depends.

EagerLoad

I would like to write about EagerLoad.
As you may have understood by now, the bottom line is that you don't want to issue SQL every time.

This alone is boring, so I'll get into a little more specifics.
"EagerLoad cannot be used unless it is a dynamic property"That's what it means.
(I think it probably doesn't make sense)
If you understand Fate's Fragarak, it's easy to imagine.

First, let's talk about the difference between dynamic properties and relation methods.
In other words, this and

$blog= new Blog; $blog->comments;

This is the difference.

$blog= new Blog; $blog->comments();

->comments becomes a dynamic property and returns a Collection.
Additionally, it has the property of ``lazy loading,'' which loads relation data only when it is accessed.
On the other hand, in the case of ->comments(), it becomes a relation object.

For dynamic properties, you cannot connect query builders, but
for relation objects
you can connect them like ->comments()->where('create_user_id', 1).

In that case, it seems better to obtain it as a relation object.
Remember the properties of EagerLoads, including with.

Yes, you are issuing a query in advance .
Since the data is being retrieved (in the relations property),
the query will not run when you write $blog->comment->create_user_id.

This is "EagerLoad cannot be used unless it is a dynamic property"
In other words,“If you want to use EagerLoad, the subsequent processing must also be made into a dynamic property, otherwise it will be meaningless!!”That's what it means.
This is how it happens, paradoxically.
This is exactly what it means to be "one who comes first rather than the other."

Then, how do you apply conditions, constraints, and searches when retrieving! ! For those who say.

$blog= Bolg::with(['comments' => function ($query) { $query->where('create_user_id', 1); }])->first(); $comments= $blog->comments ;

By doing this, you can use EagerLoad as a condition/constraint/search.
By the way, if you have conditions that are likely to be used often, it is convenient to create a method in the model file to improve readability and convenience.

The end

I hope that this article will make accessing the world's databases a little easier.