CODEWITHSUNDEEP
mysqllaravellaravel-5laravel-query-builder
Kevin Reynolds
Kevin Reynolds

Reputation: 129

How do I select columns from results of union of tables using laravel query builder?

I am trying to convert my mysql statement into laravel query builder. In the following query I am trying to do GROUP BY and ORDER BY on the columns which are fetched from the result of UNION-ing three tables

Have you guys come across anything similar to it? If yes, could you please shed some light on my issue?

SELECT staff_name, task_id, task_type, task_desc, SUM(task_multiplier) 
FROM 
(
    (
    select CONCAT(S.first_name," ",S.last_name) as staff_name, 
    `STHC`.`task_id`, IFNULL(T.type, "-") as task_type, 
    `T`.`description` as `task_desc`, 
    STHC.task_multiplier
    from `staff_task_history_calibrate` as `STHC` 
    inner join `staffs` as `S` on `STHC`.`staff_id` = `S`.`staff_id` inner join `tasks` as `T` on `STHC`.`task_id` = `T`.`id` 
    ) 
    union 
    (
    select CONCAT(S.first_name," ",S.last_name) as staff_name, 
    `STHS`.`task_id`, IFNULL(T.type, "-") as task_type, 
    `T`.`description` as `task_desc`, 
    STHS.task_multiplier
    from `staff_task_history_samples` as `STHS` 
    inner join `staffs` as `S` on `STHS`.`staff_id` = `S`.`staff_id` inner join `tasks` as `T` on `STHS`.`task_id` = `T`.`id`
    ) 
    union 
    (
    select CONCAT(S.first_name," ",S.last_name) as staff_name, 
    `STHM`.`task_id`, IFNULL(T.type, "-") as task_type, 
    `T`.`description` as `task_desc`, 
    STHM.task_multiplier
    from `staff_task_history_measures` as `STHM` 
    inner join `staffs` as `S` on `STHM`.`staff_id` = `S`.`staff_id` inner join `tasks` as `T` on `STHM`.`task_id` = `T`.`id`
    )
) combined_tables
GROUP BY staff_name, task_type, task_desc
ORDER BY staff_name, task_type, task_desc;

Upvotes: 2

Views: 1385

Answers (1)

tptcat
tptcat

Reputation: 3961

Difficult for me to test, but try:

// Build up the sub-queries
$sub1 = DB::table('staff_task_history_calibrate AS STHC')
          ->select(DB::raw(
            'CONCAT(S.first_name," ",S.last_name) as staff_name', 
            'STHC'.'task_id', 'IFNULL(T.type, "-") as task_type',
            'T'.'description' as 'task_desc', 'STHC.task_multiplier')
          )->join('staffs AS S', 'STHC.staff_id', '=', 'S.staff_id')
          ->join('tasks AS T', 'STHC.task_id', '=', 'T.id');

$sub2 = DB::table('staff_task_history_samples AS STHS')
          ->select(DB::raw(
            'CONCAT(S.first_name," ",S.last_name) as staff_name', 
            'STHS'.'task_id', 'IFNULL(T.type, "-") as task_type',
            'T'.'description' as 'task_desc', 'STHS.task_multiplier')
          )->join('staffs AS S', 'STHS.staff_id', '=', 'S.staff_id')
          ->join('tasks AS T', 'STHS.task_id', '=', 'T.id');

$sub3 = DB::table('staff_task_history_measures AS STHM')
          ->select(DB::raw(
            'CONCAT(S.first_name," ",S.last_name) as staff_name', 
            'STHM'.'task_id', 'IFNULL(T.type, "-") as task_type',
            'T'.'description' as 'task_desc', 'STHM.task_multiplier')
          )->join('staffs AS S', 'STHM.staff_id', '=', 'S.staff_id')
          ->join('tasks AS T', 'STHM.task_id', '=', 'T.id');


// Add the unions
$allUnions = $sub1->union($sub2)->union($sub3); // (Check the documentation for Unions in Query Builder)

// Get the results
$results = DB::table(DB::raw("({$allUnions->toSql()}) as combined_tables"))
       ->select('staff_name, task_id, task_type, task_desc')
       ->sum('task_multiplier')
       ->mergeBindings($allUnions) // We need to retrieve the underlying SQL
       ->groupBy('staff_name')
       ->groupBy('task_type')
       ->groupBy('task_desc')
       ->orderBy('staff_name')
       ->orderBy('task_type')
       ->orderBy('task_desc')
       ->get();

(If what you have works, I'd keep it.)

Upvotes: 4

Related Questions