My problem:
Lets say I need to find the best possibility (the max allotment an item can offer), in a certain period - 10 days for this example. The user wants to go on a holiday for 5 days.
Example resultset from current query shows the allotment per day:
+---------+------------+-----------+
| Item Id | Date | Allotment |
+---------+------------+-----------+
| 10 | 20-10-2018 | 100 |
| 10 | 21-10-2018 | 80 |
| 10 | 22-10-2018 | 100 |
| 10 | 23-10-2018 | 100 |
| 10 | 24-10-2018 | 100 |
| 10 | 25-10-2018 | 100 |
| 10 | 26-10-2018 | 100 |
| 10 | 27-10-2018 | 70 |
| 10 | 28-10-2018 | 100 |
| 10 | 29-10-2018 | 100 |
+---------+------------+-----------+
The resultset of the above example is done with the following query:
foreach($arrivalDates as $key => $date) {
$arrivalDate = $date['from'];
$departureDate = $date['till'];
$allotmentSQLArr[] = "EXISTS (SELECT 1 FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN $arrivalDate AND $departureDate AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration)";
$whereSqlArr[] = "(
EXISTS (SELECT fkItemRoom FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = $arrivalDate AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = $departureDate AND ea.departure_possible = 1)
)";
}
$query = "
SELECT
t.fkItemId,
t.arrival_date,
SUM(t.total_allotment) as total_allotment
FROM (
SELECT
ir.fkItemId,
ir.id,
ea.arrival_date,
(ea.allotment * r.max_occupancy) as total_allotment
FROM
item_room ir
INNER JOIN
room r
ON
r.id = ir.fkRoomId
INNER JOIN
availability as ea
ON
ea.fkItemRoom = ir.id
AND
ea.arrival_date BETWEEN :begin_date AND :end_date
AND
ea.allotment > 0
AND
(".implode(' OR ', $whereSqlArr).")
WHERE
(".implode(' OR ', $allotmentSQLArr).")
) as T
GROUP BY
t.fkItemId, t.arrival_date
";
Solution I'm looking for: Now I need to know if the item can offer the user a stay for 5 days and has atleast 80 allotment available per day. For the following periods 20-10-2018 till 24-10-2018, 21-10-2018 till 25-10-2018, 22-10-2018 till 26-10-2018 the max is 80. For the period 23-10-2018 till 27-10-2018 the max is 70.
In this case the item should be available, because it can offer the user the allotment in at least one period.
My own approach:
SELECT
a.id
FROM
tl_item a
WHERE
EXISTS (
SELECT
1
FROM (
SELECT
t.fkItemId,
t.arrival_date,
SUM(t.total_allotment) as total_allotment
FROM (
SELECT
ir.fkItemId,
ir.id,
ea.arrival_date,
(ea.allotment * r.max_occupancy) as total_allotment
FROM
tl_item_room ir
INNER JOIN
tl_room r
ON
r.id = ir.fkRoomId
INNER JOIN
tl_et_availability as ea
ON
ea.fkItemRoom = ir.id
AND
ea.arrival_date BETWEEN :begin_date AND :end_date
AND
ea.allotment > 0
AND
((
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1539853200 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540458000 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1539939600 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540544400 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540026000 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540630800 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540112400 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540720800 AND ea.departure_possible = 1)
) OR (
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540198800 AND ea.arrival_possible = 1 AND ((ea.is_maximum IS NULL AND ea.minimum_stay <= :duration) OR (ea.is_maximum = 1 AND ea.minimum_stay = :duration)))
AND
EXISTS (SELECT fkItemRoom FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date = 1540807200 AND ea.departure_possible = 1)
))
AND
(EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1539853200 AND 1540458000 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1539939600 AND 1540544400 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540026000 AND 1540630800 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540112400 AND 1540720800 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration) OR EXISTS (SELECT 1 FROM tl_et_availability ea WHERE fkItemRoom = ir.id AND ea.arrival_date BETWEEN 1540198800 AND 1540807200 AND allotment > 0 HAVING COUNT(ea.fkItemRoom) >= :duration))
) as T
GROUP BY
t.fkItemId,t.arrival_date
) as B
WHERE
((arrival_date BETWEEN 1539853200 AND 1540458000 and total_allotment > :allotment) OR (arrival_date BETWEEN 1539939600 AND 1540544400 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540026000 AND 1540630800 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540112400 AND 1540720800 and total_allotment > :allotment) OR (arrival_date BETWEEN 1540198800 AND 1540807200 and total_allotment > :allotment))
AND
b.fkItemId = a.id
)
I'm aware that the above will return TRUE if just ONE record matches the criteria. What I need to achieve is that the following line checks if ALL records between these dates have an allotment greater as assigned:
WHERE ((arrival_date BETWEEN 1539853200 AND 1540458000 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1539939600 AND 1540544400 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540026000 AND 1540630800 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540112400 AND 1540720800 and total_allotment >= :allotment) OR (arrival_date BETWEEN 1540198800 AND 1540807200 and total_allotment >= :allotment))
