query builder - Left outer join does not work as expected in laravel 6

Question

I have 4 tables and it's properties are given below:

1) candidates->id, name, email, skill 

2) candidate_requests->status, agent_reg_id(foreign key), candidate_id(foreign key) 
,package_type_id(foreign key).

3) package_lists->id, agent_reg_id(foreign key) 

4) orders->payment_status, candidate_id(foreign),agent_reg_id(foreign key).

Here is my controller's method:

 public function approvedCandidates()
{

    $current_agent_id=Auth::user()->id;
    $approvedCandidates=DB::table('candidate_requests')
        ->select(
             
        'candidates.id','candidates.email','candidate_requests.agent_reg_id','candidates.firstName',
        'candidates.title','candidates.skill_name', 
        'candidate_requests.candidate_id','candidate_requests.status',
        'package_lists.package_type','orders.payment_status','orders.phone')

        ->join('candidates','candidates.id','=','candidate_requests.candidate_id')
        ->join('package_lists','package_lists.id','=','candidate_requests.package_type_id')
        ->leftJoin('orders','candidates.id','=','orders.candidate_id')
        ->where('candidate_requests.agent_reg_id',$current_agent_id)
        ->where('orders.agent_reg_id',$current_agent_id)
        ->where('candidate_requests.status','=','approved')
        ->orderBy('candidates.id','ASC')
        ->get();
    dd($approvedCandidates);

    //get candidate id from above
    //and match to the order table then we can get the payment status of the specific candidate
    return view('local_agent.approvedCandidates',['approvedCandidates'=>$approvedCandidates]);
}

Result should give data for the candidate 1 and candidate 2. But it gives the result of only the candidate 2. In orders table there is no data related to the candidate 1. But in left outer join, I know that if the related table has no data then value is going to be retrieved with the null value. But it does not return anything which is related to the candidate 1. Why I could not able to get the data of the candidate 1?

question from:https://stackoverflow.com/questions/65644554/left-outer-join-does-not-work-as-expected-in-laravel-6

Answer 1

Waitting for answers