diff options
author | msquare <msquare@notrademark.de> | 2016-12-28 00:48:16 +0100 |
---|---|---|
committer | msquare <msquare@notrademark.de> | 2016-12-28 00:48:16 +0100 |
commit | 75945fdbbd0908c31921ce586308863ff467e5fe (patch) | |
tree | d7c587a82e3e2d36aa202ab9b9d51c3272acfa21 | |
parent | eccbf32f68d5cc2c7d25f4d369b70d2904239212 (diff) |
more performance using union
-rw-r--r-- | includes/model/Shifts_model.php | 31 |
1 files changed, 19 insertions, 12 deletions
diff --git a/includes/model/Shifts_model.php b/includes/model/Shifts_model.php index 61f82255..de39aa5b 100644 --- a/includes/model/Shifts_model.php +++ b/includes/model/Shifts_model.php @@ -76,8 +76,17 @@ function NeededAngeltypes_by_ShiftsFilter(ShiftsFilter $shiftsFilter, $user) { JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . " - ORDER BY `Shifts`.`start`"; - // FIXME: Use needed angeltypes on rooms! + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT `NeededAngelTypes`.*, `AngelTypes`.`id`, `AngelTypes`.`name`, `AngelTypes`.`restricted`, `AngelTypes`.`no_self_signup` + FROM `Shifts` + JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` + JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` + WHERE `Shifts`.`RID` IN (" . implode(',', $shiftsFilter->getRooms()) . ") + AND `start` BETWEEN " . $shiftsFilter->getStartTime() . " AND " . $shiftsFilter->getEndTime() . " + AND NOT `Shifts`.`PSID` IS NULL"; $result = sql_select($SQL); if ($result === false) { engelsystem_error("Unable to load needed angeltypes by filter."); @@ -92,21 +101,19 @@ function NeededAngeltype_by_Shift_and_Angeltype($shift, $angeltype) { JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . " AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . " - ORDER BY `Shifts`.`start`"); - if ($result === false) { - engelsystem_error("Unable to load needed angeltypes by filter."); - } - if (count($result) == 0) { - $result = sql_select("SELECT `NeededAngelTypes`.*, `AngelTypes`.`id`, `AngelTypes`.`name`, `AngelTypes`.`restricted`, `AngelTypes`.`no_self_signup` + AND `Shifts`.`PSID` IS NULL + + UNION + + SELECT `NeededAngelTypes`.*, `AngelTypes`.`id`, `AngelTypes`.`name`, `AngelTypes`.`restricted`, `AngelTypes`.`no_self_signup` FROM `Shifts` JOIN `NeededAngelTypes` ON `NeededAngelTypes`.`room_id`=`Shifts`.`RID` JOIN `AngelTypes` ON `AngelTypes`.`id`= `NeededAngelTypes`.`angel_type_id` WHERE `Shifts`.`SID`=" . sql_escape($shift['SID']) . " AND `AngelTypes`.`id`=" . sql_escape($angeltype['id']) . " - ORDER BY `Shifts`.`start`"); - if ($result === false) { - engelsystem_error("Unable to load needed angeltypes by filter."); - } + AND NOT `Shifts`.`PSID` IS NULL"); + if ($result === false) { + engelsystem_error("Unable to load needed angeltypes by filter."); } if(count($result) == 0) { return null; |