protarr
1 month ago

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

Posted 1 month ago by protarr

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           [email protected]
Jack   1          2           [email protected]
Anne   2          3           [email protected]
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.

Please sign in or create an account to participate in this conversation.