Gaps and island combined with a recursion

Question

I have a gaps and islands SQL problem combined with a recursion.

Background: I run a PostgreSQL database with school vacations and bank holidays from which I render calendar views for schools, city and what not. I code a lot of the logic with the programming language on top but try to move it into SQL to get better performance.

The table locations stores schools (e.g. "Example school"), federal states (e.g. "Baden-Württemberg") and countries (e.g. "Germany"). The country is a parent of the federal state. A federal state is a parent of a school. All have different school vacations and bank holidays. Example for the current December:

Vacations, bank holidays (blue) and what not are stored in the entries table. Depending on their hierarchy they are linked to different locations (e.g. country, federal state or school). Weekends (gray) belong to the country. Normal school vacations (green) to the federal state. Special events belong to the school. They often overlap.

Assuming I want to query all entries from the location with the id 2 and want to know how many days (duration) an entry has, I can do this with

SELECT
    e.starts_on,
    e.ends_on,
    e.name,
    (e.ends_on - e.starts_on + 1) AS days,
    l.name AS location_name
FROM
    entries e
    INNER JOIN locations l ON e.location_id = l.id
WHERE
    l.id = 2;

This results in

starts_on	ends_on	name	days	location_name
2022-12-21	2023-01-07	Christmas school vacation	18	Baden-Württemberg
2023-01-06	2023-01-06	Bank Holiday	1	Baden-Württemberg

But any student wants to know the total number of days there is no school. Including adjoining weekends or bank holidays.

That could be solved with Find rows with adjourning date ranges and accumulate their durations

But I have trouble to combine that with the recursion (searching for a school but actually search for a school, a federal state and a country).

Question

How can I select the days of the entry and the total_days of all the adjoining entries of either the location and of all parents of the location? And can I also aggregate the entry_ids that adjoin or overlap in one attribute too?

I want to search for the "Christmas school vacation" of the school with the id 3 and get this result:

starts_on	ends_on	name	days	location_name	total_days	aggr_location_ids	real_start	real_end
2022-12-21	2023-01-07	Christmas school vacation	18	Baden-Württemberg	19	[1, 2, 3, 4, 6, 7, 8]	2022-12-21	23-01-08

Setup with PostgreSQL

https://www.db-fiddle.com/f/jVPQoFPQw7axf7XivwpiPZ/0

CREATE TABLE locations (
  id serial PRIMARY KEY,
  name varchar(255) NOT NULL,
  parent_id integer REFERENCES locations (id)
);

CREATE TABLE entries (
  id serial PRIMARY KEY,
  starts_on date NOT NULL,
  ends_on date NOT NULL,
  name varchar(255) NOT NULL,
  location_id integer REFERENCES locations (id) NOT NULL
);
ALTER TABLE entries ADD CONSTRAINT ends_after_starts CHECK (ends_on >= starts_on);

INSERT INTO locations (name) VALUES ('Germany');
INSERT INTO locations (name, parent_id) VALUES ('Baden-Württemberg', 1);
INSERT INTO locations (name, parent_id) VALUES ('Example school', 2);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2022-12-21', '2023-01-07', 'Christmas school vacation', 2);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2022-12-25', '2022-12-26', 'Christmas', 1);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2023-01-01', '2023-01-01', 'New Year', 1);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2023-01-06', '2023-01-06', 'Bank Holiday', 2);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2022-12-17', '2022-12-18', 'Weekend', 1);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2022-12-24', '2022-12-25', 'Weekend', 1);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2022-12-31', '2023-01-01', 'Weekend', 1);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2023-01-07', '2023-01-08', 'Weekend', 1);

INSERT INTO entries (starts_on, ends_on, name, location_id) VALUES ('2023-01-14', '2023-01-15', 'Weekend', 1);

Answer 1

The query below first produces the recursive lookup for all the location entries and then, in get_vacations, searches for vacations that are adjacent to the holiday originally searched for:

with recursive cte as (
   select l.*, jsonb_build_array(l.id) tree from locations l where l.parent_id is null
   union all
   select l.*, c.tree || jsonb_build_array(l.id) from cte c join locations l on l.parent_id = c.id
),
get_vacations(id, t, h_id, r_s, r_e) as (
   select c.id, c.tree, e.id, e.starts_on, e.ends_on 
   from cte c join entries e on exists (select 1 from jsonb_array_elements(c.tree) v 
       where (v.value#>>'{}')::int = e.location_id) 
   where c.id = 3 and e.name = 'Christmas school vacation' -- SEARCH CRITERIA
   union all
   select g.id, g.t, g.h_id, least(e.starts_on, g.r_s), greatest(e.ends_on, g.r_e) 
   from get_vacations g join entries e on exists (select 1 from jsonb_array_elements(g.t) v 
      where (v.value#>>'{}')::int = e.location_id) 
    and e.starts_on = g.r_e or e.ends_on = g.r_s
)
select e.starts_on, e.ends_on, e.name, e.ends_on - e.starts_on days, 
   l.name location_name, t.r_e - t.r_s total_days, t.t aggr_location_ids, 
   t.r_s real_start, t.r_e real_end
from (select g.id, g.t, g.h_id, min(g.r_s) r_s, max(g.r_e) r_e 
      from get_vacations g group by g.id, g.t, g.h_id) t 
join entries e on e.id = t.h_id 
join locations l on l.id = (t.t ->> 1)::int

See fiddle.