Using Laravel's eloquent models, how do I get a plain left join result?

I want to avoid accessing the tables directly using the DB class.

Let's supose I have these 2 related tables.

user
  id
  name
  age
  
email
  id
  user_id
  email

I created a hasMany emails method on User model, so I can get the emails like: User::find(1)->emails. That's ok and it's working.

But what I really need in this example is to get some users and his respective emails in a plain collection or array, and I need the users that haven't emails too.

I could do User::where('age', 30)->get(), iterate over it and get the User::emails, but this approach isn't nice.

How to achieve this result, using Eloquent models, in a single list, like:

select 
  u.name,
  u.id as user_id,
  e.id as email_id,
  e.email
from user u
left join email e on u.id = e.user_id and u.age = 30

name   user_id    email_id    email
Jack   1          1           jack@localhost
Jack   1          2           jack2@local
Anne   2          3           anne@email
John   3          null        null
Karen  4          null        null

So, in this example, I need all users with age 30, even if they haven't any emails. And I need the result in a plain list as this query would return me.

Thank you.

Sergiu17

5 years ago

Level 60

User::with('emails')->where('age', 30)->get();

2 likes

MichalOravec

5 years ago

Level 75

The correct answer is what @sergiu17 gave you. Which is eager loading

$users = User::with('emails')->where('age', 30)->get();

Docs: https://laravel.com/docs/8.x/eloquent-relationships#eager-loading

But if you want to have result as in your post it should be like this

$users = DB::table('users')
    ->select('users.name', 'users.id as user_id', 'emails.id as email_id', 'emails.email');
    ->leftJoin('emails', 'users.id', '=', 'emails.user_id')
    ->where('users.age', 30)
    ->get();

Docs: https://laravel.com/docs/8.x/queries

2 likes

jlrdw

5 years ago

Best Answer

Level 75

Or for eloquent models, not query builder:

$users = User::query()->leftJoin('emails', 'users.id', '=', 'emails.user_id')
        ->select('users.name', 'users.id as user_id', 'emails.id as email_id', 'emails.email')
        ->where('users.age', 30)
        ->orderby('users.name')  //if ordering
        ->get();

But if relations are set, go with @sergiu17 answer.

2 likes

Please or to participate in this conversation.