Laravel - Search relation including null in whereHas

Question

I'm trying to find a way to search the relationship.

So, I have one to one relation (User and UserDetails, where some of the user can be without details).

So, the question is... how can I search Users by their details, but also Users without details must be included into the results.

Example: Table users: user_id, name, email - 1 John Doe [email protected] - 2 Richard Tim [email protected] - 3 Michael Bird [email protected]

Table user_details: ID, user_id, state - 1, 1, California - 2, 2, New York

and now... I want to get all users from California, and all users without state (In above case results should be John Doe and Michael Bird

Here is relation in User model:

public function details()
{
    return $this->belongsTo('App\UserDetails', 'user_id', 'user_id');
}

and search function is:

$users = $users->whereHas('details', function($query) use $state {
    $query->where('state', $state);
});

Keep in mind that I must to do that using instance of eloquent / query builder, I'm not able to use joins or raw queries.

In this case, I want to get all users from California and all non-related users, but the query returns/search only related users/user_details...

So, can someone to help me ? Thanks

UPDATE:

I'm trying like that:

$users = new User();
$users = $users->doesntHave('user_details')->orWhereHas('user_details', function($query) use($state) {
                $query->whereIn('state', $state);
            });
$users->where('created_at', '>=', $start_date);
$users->where('created_at', '<=', $end_date);

Rollick

when you edit your question add EDIT: any thing you edited!! – Defensive

Answer 1

You can use doesntHave in conjunction with orWhereHas to create the desired query:

User::doesntHave('details')->orWhereHas('details', function($query) {
    $query->where('state', '=', $state);
})->where('users.created_at', '>=', $start_date)
    ->where('users.created_at', '<=', $end_date)
    ->get();  

Ott

The problem is that I must to do that using the model/query builder, in my case I'm not able to use joins or raw queries... (I will edit my question to suggest that I must to use eloquent/query builder functionalities) – Rollick

Thanks, your solution works, but now I have problems to combine columns from the users table... If I try to search users from California then created_at from users table is ignored by the query builder... Is there any way to combine search by users and user_details while I'm using doesntHave function ? Thanks – Rollick

can you update your original question with the code you are attempting and where it goes wrong? – Ott

I edited my original question, you can see my code... Thanks. – Rollick

Yes, in my case I'm using whereDoesntHave instead of doesntHave and now everything works properly. Thanks and sorry for my late response. – Rollick

No worries, thanks for mentioning whereDoesntHave, I hadn't come across that ever, good to know about it. – Ott