Laravel Database Eloquent Left Join Table

Question

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.

Sandeep Biju · Accepted Answer · 2021-11-07 04:20:15Z

1

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'); })

edited Nov 7, 2021 at 4:20

answered Nov 5, 2021 at 5:42

Sandeep Biju

261 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Laravel Database Eloquent Left Join Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related