By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `property_unit` (
`id` int(11) NOT NULL,
`parent_unit_id` int(11) DEFAULT NULL,
`identifier` varchar(25) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `property_unit` (`id`, `parent_unit_id`, `identifier`) VALUES
(8, NULL, "Car"), (9, 8, 'Tire 1'), (10, 8, 'Tire 2');
ALTER TABLE `property_unit`
ADD PRIMARY KEY (`id`),
ADD KEY `IDX_C3A3B3698AF5044B` (`parent_unit_id`);
ALTER TABLE `property_unit`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
ALTER TABLE `property_unit`
ADD CONSTRAINT `FK_C3A3B3698AF5044B` FOREIGN KEY (`parent_unit_id`) REFERENCES `property_unit` (`id`);
COMMIT;
CREATE TABLE `property_unit_calendar` (
`id` int(11) NOT NULL,
`property_unit_id` int(11) NOT NULL,
`checkout_by` time NOT NULL COMMENT '(DC2Type:time_immutable)',
`preparation_time` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '(DC2Type:dateinterval)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `property_unit_calendar` (`id`, `property_unit_id`, `checkout_by`, `preparation_time`) VALUES
(8, 8, '11:00:00', '+P00Y00M00DT04H00M00S'), (9, 9, '11:00:00', '+P00Y00M00DT04H00M00S'), (10, 10, '11:00:00', '+P00Y00M00DT04H00M00S');
ALTER TABLE `property_unit_calendar`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `UNIQ_9EFA774A1703A75` (`property_unit_id`);
ALTER TABLE `property_unit_calendar`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;
R TABLE `property_unit_calendar`
ADD CONSTRAINT `FK_9EFA774A1703A75` FOREIGN KEY (`property_unit_id`) REFERENCES `property_unit` (`id`);
COMMIT;
CREATE TABLE `property_unit_calendar_entry` (
`id` int(11) NOT NULL,
`calendar_id` int(11) NOT NULL,
`entry_start` datetime NOT NULL COMMENT '(DC2Type:datetimetz_immutable)',
`entry_end` datetime NOT NULL COMMENT '(DC2Type:datetimetz_immutable)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO `property_unit_calendar_entry` (`id`, `calendar_id`, `entry_start`, `entry_end`) VALUES
(1, 8, '2021-07-31 16:00:00', '2021-08-03 11:00:00'),
(2, 9, '2021-08-03 16:00:00', '2021-08-07 11:00:00'),
(3, 10, '2021-08-04 16:00:00', '2021-08-06 11:00:00'),
(4, 10, '2021-08-09 16:00:00', '2021-08-10 11:00:00');
ALTER TABLE `property_unit_calendar_entry`
ADD PRIMARY KEY (`id`),
ADD KEY `IDX_7260175CA40A2C8` (`calendar_id`);
ALTER TABLE `property_unit_calendar_entry`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=101;
ALTER TABLE `property_unit_calendar_entry`
ADD CONSTRAINT `FK_7260175CA40A2C8` FOREIGN KEY (`calendar_id`) REFERENCES `property_unit_calendar` (`id`);
COMMIT;
SELECT * FROM property_unit;
| id | parent_unit_id | identifier |
|---|---|---|
| 8 | null | Car |
| 9 | 8 | Tire 1 |
| 10 | 8 | Tire 2 |
SELECT puce.id AS puce_id, puc.id as puc_id, pu.id as pu_id, pu.identifier, entry_start, entry_end FROM property_unit_calendar_entry puce
INNER JOIN property_unit_calendar puc ON puc.id = puce.calendar_id
INNER JOIN property_unit pu ON pu.id = puc.property_unit_id
WHERE pu.id IN(8,9,10)
| puce_id | puc_id | pu_id | identifier | entry_start | entry_end |
|---|---|---|---|---|---|
| 1 | 8 | 8 | Car | 2021-07-31 16:00:00 | 2021-08-03 11:00:00 |
| 2 | 9 | 9 | Tire 1 | 2021-08-03 16:00:00 | 2021-08-07 11:00:00 |
| 3 | 10 | 10 | Tire 2 | 2021-08-04 16:00:00 | 2021-08-06 11:00:00 |
| 4 | 10 | 10 | Tire 2 | 2021-08-09 16:00:00 | 2021-08-10 11:00:00 |
WITH tab AS(
SELECT
c.*,
SUM(CASE WHEN prev_end < entry_start THEN 1 ELSE 0 END) over(
ORDER BY property_unit_id, entry_start
) AS grouping
FROM (
SELECT
d.*,
MAX(entry_end) OVER (
PARTITION BY property_unit_id
ORDER BY entry_start ROWS
BETWEEN unbounded preceding AND 1 preceding
) AS prev_end
FROM (
SELECT
puc.property_unit_id,
puce.entry_start,
puce.entry_end
FROM property_unit_calendar_entry puce
JOIN property_unit_calendar puc ON
puce.calendar_id = puc.id
UNION
SELECT
pu.id AS `property_unit_id`,
puce.entry_start,
puce.entry_end
FROM property_unit pu
JOIN property_unit_calendar puc ON pu.id = puc.property_unit_id
JOIN property_unit_calendar_entry puce ON puc.id = puce.calendar_id
UNION
SELECT
pu.parent_unit_id AS `property_unit_id`,
puce.entry_start,
puce.entry_end
FROM property_unit pu
| property_unit_id | available_from | available_til |
|---|---|---|
| 8 | null | 2021-07-31 16:00:00 |
| 8 | 2021-08-03 11:00:00 | 2021-08-03 16:00:00 |
| 8 | 2021-08-07 11:00:00 | 2021-08-09 16:00:00 |
| 8 | 2021-08-10 11:00:00 | null |
| 9 | null | 2021-08-03 16:00:00 |
| 9 | 2021-08-07 11:00:00 | null |
| 10 | null | 2021-08-04 16:00:00 |
| 10 | 2021-08-06 11:00:00 | 2021-08-09 16:00:00 |
| 10 | 2021-08-10 11:00:00 | null |