Ad

Laravel Union And Join In The Query

I have 3 tables

  1. send_to_employees
  2. onprocess
  3. employees

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.

Ad

Answer

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.

Ad
source: stackoverflow.com
Ad