Laravel 5.6 Eloquent ORM where join table

Question

I am dealing with Eloquent ORM collections and query builders. I am trying to figure out how to join and use "where" in a collection, like in query builder.

For example, I have the following tables:

Users:

ID  |  Name  |  Last name
-------------------------
 1  | Martin | Fernandez
 2  | Some   | User

Persons:

ID  |  Nick  |  User_ID  |  Active
----------------------------------
 1  | Tincho | 1         |  1

Companies:

ID  | Name   | User_ID   |  Active
----------------------------------
 1  | Maramal| 1         |  0
 2  | Some   | 2         |  1

This is an example, the tables I am working on have more than 30 columns each one. I want to select all the user that are active.

Usually I would do a query like:

SELECT *
FROM users
LEFT JOIN persons ON users.id = persons.user_id
LEFT join companies ON users.id = companies.user_id
WHERE persons.active = 1
OR companies.active = 1

That can be translated to Laravel Query Builder like:

DB::table('users')
   ->leftJoin('persons', 'users.id', '=', 'persons.user_id')
   ->leftJoin('companies', 'users.id', '=', 'companies.user_id')
   ->where('persons.active', 1)
   ->orWhere('companies.active', 1)
   ->get();

But what I want to use is a Laravel Eloquent ORM Collection, until now I am doing the following:

$users= User::orderBy('id',' desc')->get();

foreach($users as $k => $user) {
    if($user->company && !$user->company->active || $user->person && !$user->person->active) {
        unset($users[$k]);
    }
    ... and here a lot of validations and unsets ...
 }

But I know that at this point, I already grabbed all the users instead those who are active.

How would I achieve what I did with query builder within a collection? Thanks in advance.

Paras Paras · Accepted Answer · 2018-02-27T21:01:31

This should do it:

$users = User::whereHas('companies', function($q) {
    $q->where('active', true);
})->orWhereHas('persons', function($q) {
    $q->where('active', true);
})->with(['companies', 'persons'])->orderBy('id', 'desc')->get();

Laravel 5.6 Eloquent ORM where join table

1 Answers