Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

KevinReynolds's avatar

Using laravel query builder, how do I select columns from the result of sub-query which is a union of tables ?

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

  • staff_task_history_calibrate
  • staff_task_history_samples
  • staff_task_history_measures 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;
0 likes
2 replies
jimmck's avatar
jimmck
Best Answer
Level 13

@KevinReynolds Your sub-selects are your 'Tables' give them an alias and you can access the columns.

 (
    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` 
    ) as TBL_STCH 

Use the alias TBL_STCH (or your name) as the column prefix. As I do not use Eloquent it left up to you and those to convert it.

Please or to participate in this conversation.