Why can't Laravel/Eloquent use JOIN for Eager Loading?

Question

<?php

class Cat extends Eloquent {

    public function user() {
        return $this->belongsTo('User');
    }
}

class User extends Eloquent {

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

Now:

$cats = Cat::with('user')->get();

Performs 2 queries:

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

Why can't it just do:

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

For those saying that there are both id columns in the table, that could be easily avoided with aliases:

select 
    c.id as cats__id,
    c.name as cats__name,
    c.user_id as cats__user_id,
    b.id as users__id,
    b.name as users__name
from cats c
inner join users b on b.id = c.user_id

UPDATE

Someone pointed out that Eloquent doens't know the columns of the tables from the models, but I guess they could provide a way to define them in the model so then it could use aliases and do a proper join instead of an extra query.

Answer 1

My guess is that this allows for eager loading multiple one to many relationships. Say, for 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 want to eager load them both with the User:

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

There is no join that would work to combine these into a single query. However, doing a seperate 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 this methodology may produce an extra query in some simple circumstances, it provides the ability to eager load more complex data where the join method will fail.

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

Answer 2

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

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

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

and its not one single query as

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

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

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

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

with a join, it will be like

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

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

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

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

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

For Scalability purpose if following sharding architecture, will have to remove the JOIN's. This was practiced 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

Answer 3

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