Laravel Eloquent join vs with

Question

I see that join is (by default inner join) and its returning all columns but it takes almost the same time as with keyword for just 1000 data.

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

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

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

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

which is the best way to return a list of users with a pagination for a REST API ? eager loading seems promising but its with a sub query.

if do with eager loading, this is how i will be filtering. need to use whereHas

if($request->filled('first_name')){
        $query->whereHas('profile',function($q) use ($request){
            $q->where('first_name','like','%'.request('first_name').'%');
        });
    }

but if used Join, its less lines of code.

  if ($request->filled('first_name')) {
            $users = $users->where('first_name', 'LIKE', "%$request->first_name%");
        }

laravel version is 5.7

Answer 1

Eloquent is Laravel's implementation of Active Record pattern and it comes with all its strengths and weaknesses. It is a good solution to use when you process a single entity in a CRUD manner - that is, read from database or create a new entity and then save it or delete. You will benefit a lot from Eloquent's features such as dirty checking (to send SQL UPDATE only for the fields which have been changed), model events (e.g. to send administrative alert or update statistics counter when someone has created a new account), traits (timestamps, soft deletes, custom traits) eager/lazy loading etc.

But, as you already know, it comes with some performance price. When you process a single or just a few records, there is nothing to worry about. But for cases when you read lots of records (e.g. for datagrids, for reports, for batch processing etc.) the plain DB is better approach.

For our application we are doing exactly that - using Laravel's Eloquent in web forms to process a single record and using DB (with SQL views) to retrieve data for grids, export etc.

When it comes to performance and the application grows, for the sake of comparison, take a loot at the following tables:

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