Why Laravel does not optimize model queries automatically?


Till today I was relying on Laravel relationships, but since I opened mysql logs I was very disappointed.

When I execute code

Company::with(['users', 'machines'])->get()

mysql.log looks this way

select * from `company` where `company`.`id` = '48' limit 1
select * from `user` where `user`.`company_id` in ('48')
select * from `machine` where `machine`.`company_id` in ('48')

Why Laravel does not use joins for eager fetching? Also, are there any ways of improving perfomance and still using Laravel Models?

I know that Doctrine ORM eager loading works pretty nice by using joins.


Thank you for your help.

Answer

If you really want to use joins instead of the Eloquent computed queries, I suppose you could just use the fluent query builder (that comes shipped with Laravel through the DB facade) and stick that code into a method of your model to keep everything nice and SRP.

For instance:

class Company extends Model {
    public function sqlWithJoin() {
        $users = DB::table('company')
        ->leftJoin('user', 'company.id', '=', 'user.company_id')
        ->get();

        return $users;
    }
}

This would generate a proper join query for you.

As for why you would want to do this, you would have to benchmark both options to see which one gives you the best performance for your specific data. I wouldn't generalize that one option always has better/worse performance than the other.

source: stackoverflow.com
js interview questions