CODEWITHSUNDEEP
phpmysqllaravel
Germeloper
Germeloper

Reputation: 71

Laravel Database Eloquent Left Join Table

Hi I would like to ask is there any way to refer the 2 foreign key to 1 primary table id?

This is my code.

$job_postings = JobPosting::select(
    'job_postings.id as job_posting_id',
    'job_postings.qualifications',
    'job_postings.created_at',
    'job_postings.updated_at',
    'job_postings.created_by',
    'job_postings.approved_by',
    'dealerships.dealership_name',
    'departments.department_name',
    'positions.position_name',
    'users.name as created_by_name', (**This is the issue please help**)
    'users.name as approved_by_name', (**This is the issue please help**)
    DB::raw("case when job_postings.status = 0 then 'Pending' else 'Approved' end status"),
)
->leftJoin('dealerships', 'job_postings.dealership_id', '=', 'dealerships.id')
->leftJoin('departments', 'job_postings.department_id', '=', 'departments.id')
->leftJoin('positions', 'job_postings.position_id', '=', 'positions.id')
->leftJoin('users', 'job_postings.created_by', '=', 'users.id', 'job_postings.approved_by', '=', 'users.id')
->where('job_postings.id', $jp_id)
->get();

$data = [];
foreach($job_postings as $j => $job_posting){
    $data[] = [
        'job_posting_id'  => $job_posting->job_posting_id, 
        'dealership_name' => $job_posting->dealership_name,
        'department_name' => $job_posting->department_name,
        'position_name'   => $job_posting->position_name,
        'qualifications'  => $job_posting->qualifications,
        'status'          => $job_posting->status,
        'created_by'      => $job_posting->created_by_name,
        'approved_by'     => $job_posting->approved_by_name,
        'created_at'      => $job_posting->created_at->format('D, d M Y').date(' | h:i A', strtotime($job_posting->created_at)),
        'updated_at'      => $job_posting->updated_at->format('D, d M Y').date(' | h:i A', strtotime($job_posting->updated_at)),
    ];
}

Under the last leftJoin

->leftJoin('users', 'job_postings.created_by', '=', 'users.id', 'job_postings.approved_by', '=', 'users.id')

the approved_by is a foreign key pointing to users id and created_by is also a foreign key pointing to users id.

What I am aiming at is to get the name of the user but in separate reference. Somewhat similar to this analogy .

'users.name = created_by as created_by_name', 
'users.name = approved_by as approved_by_name', 

approved_by = user.id => user.name
created_by = users.id => user.name

Thanks.

Upvotes: 0

Views: 179

Answers (1)

Sandeep Biju
Sandeep Biju

Reputation: 26

leftJoin('users','job_postings.created_by','=','users.id', 'job_postings.approved_by', '=', 'users.id')

Try this,

->leftJoin('users', function($join){ $join->on('users.id', '=', 'job_postings.created_by'); $join->on('users.id', '=', 'job_postings.approved_by'); })

Upvotes: 1

Related Questions