Level 75
May 10, 2016
2
Level 9
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;
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.