Reputation: 25
I have 3 tables
I want to do union of the two table send_to_employees
and onprocess
. After that I want to join the result with the employees
table.
I have done it in a raw SQL query like the following already:
SELECT *
FROM (
SELECT
send_to_employees.caseid,
send_to_employees.docs,
send_to_employees.helper,
send_to_employees.employee_id
FROM send_to_employees
UNION
SELECT
onprocess.caseid,
onprocess.docs,
onprocess.helper,
onprocess.employee_id
FROM onprocess
) t1
INNER JOIN employees
ON employees.employee_id = t1.employee_id
But I can't figure out how to do it with Laravel. Please help me to write this query using the query builder of Laravel 5.8.
Upvotes: 2
Views: 5165
Reputation: 6544
What you are looking for is something like this:
DB::query()
->fromSub(
DB::table('send_to_employees')
->select([
'caseid',
'docs',
'helper',
'employee_id'
])
->union(
DB::table('onprocess')
->select([
'caseid',
'docs',
'helper',
'employee_id'
])
),
'inner'
)
->join('employees', 'employees.employee_id', '=', 'inner.employee_id')
->select(['inner.*', 'employees.*'])
->get();
The key to success is using fromSub()
which allows you to perform a subquery. Please make sure to not use ->get()
in a sub query though as it would wrap a collection and therefore fail.
Upvotes: 7
Reputation: 902
Try this code snippet. I think it should work. I am using query builder
//getting all record from table
$data['recorddisplay'] = DB::table('send_to_employees')
->leftjoin('onprocess', 'send_to_employee.caseid', '=', 'onprocess.caseid')
->leftjoin('employees', 'send_to_employee.employee_id', '=', 'employees.employee_id')
->get();
Upvotes: 0