Small understanding problem when using "with()"

Hey guys,

I have a small understanding problem when it comes to using with(). When requesting data in the frontend, I fully understand it:

$users = User::with("role")->get()

This will result in a select into the users table as well as a select into the roles table using the ids of the selected users. Makes sense in the frontend.

But currently I am asking myself if this makes sense when e.g. used within a job class?

$users = User::with("role")->get();
foreach($users as $user) {
	//do something with $user->role->name;
}

I am not sure if Laravel is using the with("role") here correctly and not just fetching the role data using one select for each user within the loop. In that case it would make more sense to use a join and write something like this:

$users = User::select("users.*","roles.name as role_name")->join("roles","users.role_id","=","roles.id")->get();
foreach($users as $user) {
	//do something with $user->role_name;
}

This is a very simple example, I hope you can get what I mean. Just need some clarification on this problem.

Best, Damian

Sinnbeck

4 years ago

Level 102

If you use with or load on the query and pass that in, then it will be used. You can always ensure that it is indeed loaded before using it

$users->loadMissing('roles');

But as I said, this code will use the role that you loaded. So just 2 queries are done.

$users = User::with("role")->get();
foreach($users as $user) {
	//do something with $user->role->name;
}

tykus

4 years ago

Level 104

Nothing to stop you writing a query with a JOIN if that's your preference.

In the example that you share it is (from the User perspective) a one-to-one relationship (a User has one Role) - so a JOIN can easily replace the second query (depending on what you want to do with the Role instance (or lack of a Role instance). However, sometimes we are eager-loading a one-to-many relationship - where a JOIN would make less sense.

Ultimately, eager-loading using with works as expected (2 queries only); but if you don't, the $user->role will result in a new query for every $user in $users

Damian89

4 years ago

Level 4

Thank you, just wanted to make sure. Best, D

Snapey

4 years ago

Level 122

perhaps read up on n+1 issues, you will see that the with method allows eloquent to realise the nested data it will need so that it does not need to do a select for every iteration of the loop

imagine I send you to the shop. better to give you a list than to wait for you to come back then asking for something else

if you don't 'eager load' then eloquent has no option but to hit the database each time you ask for a user’s role

You could join, but being able to load nested relations is a key advantage of an ORM like eloquent

Please or to participate in this conversation.