Laravel Eloquent function with() is the same as join?-CodePudding

I used to think that when i am using with() function with relations it is the same what join is, so for example if I have User::with('shop') it will join this second table and it is one query. Now when I turned on sql debug log I can see it is not join action, just another query without bindings.

My question is: is joining table on my own is faster than using with() ?

CodePudding user response：

Yes join works faster because with the with operator eager loading is used for the eloquent relationships. Eloquent model uses toArray() which merges own attributes and relationships in one representation, thus it performs worst.

To double check:

\DB::enableQueryLog();
$builtQuery->get()
dd(\DB::getQueryLog());

CodePudding user response：

$user->join(‘shop’, ‘users.id’, ‘=’, ‘shop.user_id’) - generates the below query.

select * from `users` inner join `shop` on `users`.`id` = `shop`.`user_id` where `first_name` LIKE '%a%'`

User::with(‘shop’) - this eager loading outputs the below query

select * from `users` where exists (select * from `shop` where `users`.`id` = `shop`.`user_id` and `first_name` LIKE '%a%')

Comparison of select operation average response time between Eloquent ORM and Raw SQL

Eloquent ORM average response time

Joins | Average (ms) 

1     | 162,2
3     | 1002,7
4     | 1540,0

Result of select operation average response time for Eloquent ORM

Raw SQL average response time

Joins | Average (ms)
1     | 116,4 
3     | 130,6 
4     | 155,2

Result of select operation average response time for Raw SQL

So, join query is faster than using with('shop')