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

"Anyone who has never written a raw query should seriously be concerned,"
is what my seniors and superiors have been telling me a lot lately. This is Enoki from the Systems Development Department.
(By the way, I've been put in charge of MySQL training for new recruits, so I'm feeling a serious sense of urgency.)

N+1 problem

(Leaving my own story aside)
Let's assume that the following relationship was established.

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

Get the Bolg model

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

Here the following query is issued:

"select * from `blogs`"

By looping further, we can get all the Comment models associated with each Blog model

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

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

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

So, the initial query to retrieve all records is issued once, plus N queries in total.
This is the 1+N, or rather, N+1 problem.

The problem is that, naturally, as many queries as there are chapters, the load is increased and this is simply a problem

join and with avoid N+1

We have now reached the main point

As mentioned earlier, issuing a query each time is extremely inefficient, so we use (left) join or with.
(Incidentally, there are cases where it is better to retrieve the data each time, so choose carefully.)
These can be used to prevent a large number of queries from being sent.
However, they each do something different.

What is join?

The `join` statement performs an inner join.
It retrieves data common to both tables and excludes everything else.

`leftJoin` performs a left outer join.
It returns all rows from the left table and any matching rows from the right table. It retrieves
data that matches the join condition while retaining all the data from the left table.

These joins are a SQL feature.
They combine multiple tables within a query.
The query looks something like this.

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

Amazingly, it only requires one query.
It's incredible progress!

So what about with?

with

A query using `with` takes only two steps. It's
slower than `join`, but considering that a loop can run N+1 times, it's a tremendous improvement.

Incidentally, the string used as an argument to `with` is the name of the relationship definition method in the model file.
(It's essentially the same as the name of the model being related to, but be careful because you can't use `with` unless it's defined as a method.)

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

Looking at the query, we can see that using whereIn allows us to retrieve all the data at once

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 uses of each

We obtained the model using two methods, so let's take a look at the characteristics of each

● (left) The Join command
merges the models based on the join conditions.
Join is an SQL function used to retrieve data from multiple tables at once.
When retrieving data,data matching the join conditions is created.
For example, you can think of it as creating the same number of final data as the Comment model.
This can result in invalid data when joining multiple tables.

The ability to acquire the data all at once is certainly appealing, and
it's ideal when you don't need it for a model or when you want simple, flat data.

● The `with`
model is retrieved as an associative array without being merged.
in the ORMpre-loading the model relationshipsThis solves the N+1 problem by

For example, a Comment model associated with a Bolg model would exist under a property called `relations`.
Since models are retrieved nested in a hierarchical order of parent-child relationships, they are not merged.
Related models and data are retrieved at once, avoiding redundant data.
This is also useful when you want to treat the retrieved data directly as a model.

By the way, with can be used to retrieve relationships as long as they continue, such as (ModelA.ModelB.ModelC.), but since nesting means that all passed models will be included as outer shells, you will eventually need to remove them by unset or similar

Summary and digression

Now that all the information is out, I'll summarize it and then add a little digression before concluding

summary

What did you think?
Since a lot of information came up, I've put it into a table.

I don't know which one is truly useful!
While the result is roughly the same—retrieving data from multiple tables—the methods used are vastly different.

So, the conclusion is a rather commonplace one: "Change the acquisition method according to the data format you want."
The easiest way towhether you want the model in its original form or if it can be combineddetermine this is to decide
Alternatively,between nested and flat formats based on which format will be easier to use in future processing.

Furthermore,adjust it depending on the total amount of data you want to retrieveI think you should
`with` reads the relationships, so the deeper the nesting, the slower it becomes compared to `join`.
In short, it depends on the situation.

A side note (EagerLoad)

I'd like to write about EagerLoad.
You may have already understood this, but basically, it means you don't want to issue SQL queries every time.

This is a bit boring, so let me go into more detail
"EagerLoad is only available for dynamic properties"That is what it means
(Maybe that doesn't make sense)
First, let's talk about the difference between dynamic properties and relationship methods
In other words, this and

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

This is the difference

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

->comments() returns a dynamic property, a Collection. It
also has a "lazy loading" property, meaning the relation data is only loaded when accessed.
In contrast, ->comments() returns a relation object.

You can't chain query builders together with dynamic properties, but
->comments()->where('create_user_id', 1)
you can chain them together like this:

In that case, it seems better to obtain it as a relation object, right?
Now, remember the properties of EagerLoad and other related methods, including `with`.

Yes,you've issued a query beforehand,right?
Since you've retrieved the data (in the `relations` property),
the query won't run when you write `$blog->comment->create_user_id`.

This
means that "EagerLoad cannot be used unless it is a dynamic property,"If you use EagerLoad, it's pointless unless the subsequent processing is also done with dynamic properties!!"in other words,
It's a paradoxical situation.

So how do you apply conditions, restrictions and searches when retrieving data?!

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

This way, you can combine EagerLoad with conditions, constraints, and searches.
By the way, if you have conditions that you're likely to use often, you can turn them into methods in the model file, which will improve readability and make it more convenient.

The end

I hope this article will make access to databases around the world a little easier