Laravel 7 Eloquent models - (not being able to do where outside table) Unable to find column on businessDeveloper.user_id, select * from opportunities

Question

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'businessDeveloper.user_id' in 'where clause' (SQL: select * from opportunities where businessDeveloper.user_id = 3)

Hello, I'm using Laravel 7 and eloquent models for a school project. We were told not to use joins and besides making a custom JSON string with the formatted data which will take a lot of work since I have the same problem for other functionalities as well. I got no clue how to solve my problem. The tables are like this: Opportunities(business_developer_id FK) belongTo-> businessDevelopers(user_id FK) belongTo-> user So I try to show all opportunities for a logged in business developer. But this doesn't seem to work:

public function qryOpportunities()
    {
        $opportunities = Opportunity::where('businessDeveloper.user_id', Auth::id())->with('businessDeveloper.user', 'consultantOpportunity.consultant.user', 'contactPerson', 'customer', 'headOfDepartment.user')->get();
        return $opportunities;
    }

A solution to show all opportunities from the logged-in business developer is much appreciated. Thanks in advance.

Answer 1

Try ->whereHas() :

public function qryOpportunities()
{
    $opportunities = Opportunity::whereHas('businessDeveloper', function($q) {
        $q->where('user_id', Auth::id());
    })
    ->with('businessDeveloper.user', 'consultantOpportunity.consultant.user', 'contactPerson', 'customer', 'headOfDepartment.user')
    ->get();
    return $opportunities;
}

You can check the docs of whereHas() here: https://laravel.com/docs/7.x/eloquent-relationships#querying-relationship-existence

Answer 2

I suggest you use this code:

public function qryOpportunities()
{
    return Opportunity::query()
        ->whereHas('businessDeveloper', function($query) {
        $query->where('user_id', Auth::id());
    })->with('businessDeveloper.user', 'consultantOpportunity.consultant.user', 'contactPerson', 'customer', 'headOfDepartment.user')
      ->get();
}