[ACCEPTED]-Why can't Laravel/Eloquent use JOIN for Eager Loading?-eager-loading

Accepted answer

Score: 12

My guess is that this allows for eager loading 11 multiple one to many relationships. Say, for 10 instance, we also had a dogs table:

class User extends Eloquent {

    public function cats() {
        return $this->hasMany('Cat');
    }

    public function dogs() {
        return $this->hasMany('Dog');
    }
}

Now we 9 want to eager load them both with the User:

$users = User::with('cats','dogs')->get();

There 8 is no join that would work to combine these 7 into a single query. However, doing a seperate 6 query for each "with" element does work:

select * from `users`
select * from `cats` where `user`.`id` in ('1', '2', 'x')
select * from `dogs` where `user`.`id` in ('1', '2', 'x')

So, while 5 this methodology may produce an extra query 4 in some simple circumstances, it provides 3 the ability to eager load more complex data 2 where the join method will fail.

This is 1 my guess as to why it is this way.

Score: 3

I think, join query approach has a fatal 24 drawback when you want to use LIMIT and/or 23 OFFSET.

$users = User::with('cats')->get() - this 22 will output the below 2 queries.

select * from `users`
select * from `cats` where `user`.`id` in ('1', '2', 'x')

and its 21 not one single query as

select * from users inner join cats on cats.user_id = users.id

but lets say, we 20 need to paginate this record set.

User::with('cats')->paginate(10) - this 19 will output the below 2 queries with limit.

select * from `users` limit 10
select * from `cats` where `user`.`id` in ('1', '2', 'x')

with 18 a join, it will be like

select * from users inner join cats on cats.user_id = users.id limit 10

it will fetch 10 17 records but it does not mean 10 users, because 16 every user can have multiple cats.

Also another 15 reason i think is, a relation between relational 14 db and NOSQL db can be easily implemented 13 with the separated query approach

Also as 12 previous answer, id is ambiguous, and you 11 would have to prefix every statement with 10 the table name which is not desired.

On the 9 other hand, JOIN is expensive than EXISTS 8 and EXISTS is faster because it doesn't 7 order RDBMS to fetch any data, just check 6 whether relevant rows exist. EXISTS is used 5 to return a boolean value, JOIN returns 4 a whole other table.

For Scalability purpose 3 if following sharding architecture, will 2 have to remove the JOIN's. This was practiced 1 by pinterest during the scaling. http://highscalability.com/blog/2013/4/15/scaling-pinterest-from-0-to-10s-of-billions-of-page-views-a.html

Score: 1

cats and users likely both have a column named id, leaving 3 your suggested query ambiguous. Laravel's 2 eager loading uses an additional query, but 1 avoids this potential mishap.

Source: stackoverflow.com

[ACCEPTED]-Why can't Laravel/Eloquent use JOIN for Eager Loading?-eager-loading

Laravel 5 eager loading with limit

Call to a member function addEagerConstraints() on integer

Eager loading with route model binding

Metadata object handling in Laravel

Hash::make('password') returning different results at every call

Undefined property in Laravel using Eloquent

Laravel Eloquent - Using pivot table's id as a foreign key in another table

How to create Laravel Model without Eloquent?

How do I use Laravel's chunk to avoid running out of memory?

How do you insert related polymorphic models in Laravel 4