CODEWITHSUNDEEP
laravellaravel-query-builderlaravel-5.8
Saddam Hussain
Saddam Hussain

Reputation: 25

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.

Upvotes: 2

Views: 5165

Answers (2)

Namoshek
Namoshek

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

Julius Fasema
Julius Fasema

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

Related Questions