Why are these two queries different ?

Hello,

A User has a many to many relationship with Company and the company_user pivot table has these fields : company_id, user_id, role.

I didn't know whereRelation(), I just disovered it and tried to use it.

I need to retrieve all the users which are attached to the company of the authenticated user and have the trainer role. The first query works fine but not the second one.

I wonder why these two queries are different.

Can you explain me why the two queries are not equal ?

First one

$trainers = User::whereHas('companies', function ($query) {
    $query->where('company_id', auth()->user()->company->id)->where('role', config('grapp.constants.roles.trainer'));
})->orderBy('name')->get();

Second one

$trainers = User::whereRelation('companies', 'company_id', auth()->user()->company->id)->whereRelation('companies', 'role', config('grapp.constants.roles.trainer'));
})->orderBy('name')->get();

Thanks a lot ;).

tykus

3 years ago

Best Answer

Level 104

You have two separate whereRelation constraints on the User query (meaning two separate where exists constraints with separate sub-queries); whereas the whereHas approach there is only one where exists constraint sub-query and in that sub-query you have two constraints. They are very different because the second exists query is not scoped to the user's company

SELECT * FROM users 
WHERE EXISTS(SELECT * FROM companies WHERE company_id = ? AND role = ?) 
ORDER BY name;
// versus
SELECT * FROM users 
WHERE EXISTS(SELECT * FROM companies WHERE company_id = ?) 
AND EXISTS(SELECT * FROM companies WHERE role = ?) 
ORDER BY name;

1 like

vincent15000

3 years ago

Level 63

@tykus Do you mean that the two contraints are taken as where or where ?

I though that whereRelation() following another whereRelation() will do something like where and where. Exactly like two simple where().

Is it possible to do the same with whereRelation() ?

vincent15000

3 years ago

Level 63

@tykus I just tried this and it gives the same result.

$trainers = User::whereRelation('companies', function ($query) {
    $query->where('company_id', auth()->user()->company->id)->where('role', config('grapp.constants.roles.trainer'));
})->orderBy('name')->get();

Just replacing whereHas by whereRelation.

Are whereHas by whereRelation similar ?

tykus

3 years ago

Level 104

@vincent15000 see my updated reply above and perhaps you will understand the difference.

whereRelation delegates to whereHas under the hood, so yes they are the same

// QueriesRelationships
    public function whereRelation($relation, $column, $operator = null, $value = null)
    {
        return $this->whereHas($relation, function ($query) use ($column, $operator, $value) {
            if ($column instanceof Closure) {
                $column($query);
            } else {
                $query->where($column, $operator, $value);
            }
        });
    }

1 like

vincent15000

3 years ago

Level 63

@tykus Ok thank you, all is clear for me now ;).

tykus

3 years ago

Level 104

@vincent15000 no problem

Please or to participate in this conversation.