Query to get the next free slot #71

New Issue

ken_fallon · 2024-12-26T17:40:34Z

ken_fallon commented

2024-12-26 17:40:34 +00:00

We need to get the next_free_slot, and this needs to take into account the eps and reservations table.

This gives the next free slot from the eps table

SELECT id + 1 FROM eps mo WHERE NOT EXISTS ( SELECT NULL FROM eps mi WHERE mi.id = mo.id + 1 ) ORDER BY id LIMIT 1"

But that also needs to be combined with the reservations table

SELECT id as ep_num FROM eps UNION ALL ( SELECT ep_num FROM reservations ) ORDER BY ep_num DESC

The hpr.sql file can be downloaded at https://hackerpublicradio.org/hpr.sql

We need to get the `next_free_slot`, and this needs to take into account the eps and reservations table. This gives the next free slot from the eps table `SELECT id + 1 FROM eps mo WHERE NOT EXISTS ( SELECT NULL FROM eps mi WHERE mi.id = mo.id + 1 ) ORDER BY id LIMIT 1"` But that also needs to be combined with the reservations table `SELECT id as ep_num FROM eps UNION ALL ( SELECT ep_num FROM reservations ) ORDER BY ep_num DESC ` The `hpr.sql` file can be downloaded at https://hackerpublicradio.org/hpr.sql

rho_n commented

2024-12-27 17:01:26 +00:00

The following UNIONS the existing episodes from the two tables from the current date forward. It generates a list of all dates from the current date to the furthest reserved date and then determines the next open slot by taking the closest date with no episode from the LEFT JOIN of list of all future dates to list of reserved dates:

Tested MySQL 8.0

WITH RECURSIVE
reserved_dates AS (
	SELECT date AS 'reserve_date', id AS 'ep_num'
	FROM eps
	WHERE date >= CURRENT_DATE()
	UNION
	SELECT ep_date AS 'reserve_date', ep_num
	FROM reservations
	WHERE ep_date >= CURRENT_DATE() 
),
reserve_max AS (
	SELECT MAX(reserve_date) AS max_date
	FROM reserved_dates
),
reserve_min AS (
	SELECT MIN(reserve_date) AS min_date
	FROM reserved_dates
),
generate_upcoming_dates (n, i) AS
(
	SELECT CURDATE() AS 'n', 1 AS 'i'
	UNION ALL
	SELECT DATE_ADD(n, INTERVAL 1 DAY), i+1 FROM generate_upcoming_dates
	WHERE i <= (SELECT TO_DAYS(max_date) FROM reserve_max LIMIT 1) - (SELECT TO_DAYS(min_date) FROM reserve_min LIMIT 1)
),
upcoming_dates AS (
	SELECT n AS 'date', i AS 'index' FROM generate_upcoming_dates
)
SELECT MIN(upcoming_dates.date) AS 'next_open_slot' 
FROM upcoming_dates
LEFT JOIN reserved_dates
ON reserved_dates.reserve_date = upcoming_dates.date
WHERE WEEKDAY(upcoming_dates.date) IN (0, 1, 2, 3, 4) AND reserve_date IS NULL

Tested with SQLite 3.37

WITH reserved_dates AS (
	SELECT [date] AS [reserve_date], [id] AS [ep_num]
	FROM [eps]
	WHERE [date] >= date()
	UNION
	SELECT [ep_date] AS [reserve_date], [ep_num]
	FROM [reservations]
	WHERE [ep_date] >= date()
),
reserve_max AS (
	SELECT MAX(reserve_date) AS [max_date]
	FROM reserved_dates
),
reserve_min AS (
	SELECT MIN(reserve_date) AS [min_date]
	FROM reserved_dates
),
  generate_upcoming_dates(x) AS (
     SELECT 0
     UNION ALL
     SELECT x+1 FROM generate_upcoming_dates
      LIMIT (SELECT (((SELECT julianday(max_date) FROM reserve_max limit 1) - (SELECT julianday(min_date) FROM reserve_min limit 1))) + 1)
  ),
upcoming_dates AS (
	SELECT date((SELECT julianday(min_date) FROM reserve_min limit 1), '+' || x || ' days') as date FROM generate_upcoming_dates
)
SELECT MIN(date) AS [next_open_slot] FROM upcoming_dates
LEFT JOIN reserved_dates
ON [reserved_dates].reserve_date = [upcoming_dates].[date]
WHERE strftime('%w', date) IN ('1', '2', '3', '4','5') AND reserve_date IS NULL

The following UNIONS the existing episodes from the two tables from the current date forward. It generates a list of all dates from the current date to the furthest reserved date and then determines the next open slot by taking the closest date with no episode from the LEFT JOIN of list of all future dates to list of reserved dates: Tested MySQL 8.0 ``` WITH RECURSIVE reserved_dates AS ( SELECT date AS 'reserve_date', id AS 'ep_num' FROM eps WHERE date >= CURRENT_DATE() UNION SELECT ep_date AS 'reserve_date', ep_num FROM reservations WHERE ep_date >= CURRENT_DATE() ), reserve_max AS ( SELECT MAX(reserve_date) AS max_date FROM reserved_dates ), reserve_min AS ( SELECT MIN(reserve_date) AS min_date FROM reserved_dates ), generate_upcoming_dates (n, i) AS ( SELECT CURDATE() AS 'n', 1 AS 'i' UNION ALL SELECT DATE_ADD(n, INTERVAL 1 DAY), i+1 FROM generate_upcoming_dates WHERE i <= (SELECT TO_DAYS(max_date) FROM reserve_max LIMIT 1) - (SELECT TO_DAYS(min_date) FROM reserve_min LIMIT 1) ), upcoming_dates AS ( SELECT n AS 'date', i AS 'index' FROM generate_upcoming_dates ) SELECT MIN(upcoming_dates.date) AS 'next_open_slot' FROM upcoming_dates LEFT JOIN reserved_dates ON reserved_dates.reserve_date = upcoming_dates.date WHERE WEEKDAY(upcoming_dates.date) IN (0, 1, 2, 3, 4) AND reserve_date IS NULL ``` Tested with SQLite 3.37 ``` WITH reserved_dates AS ( SELECT [date] AS [reserve_date], [id] AS [ep_num] FROM [eps] WHERE [date] >= date() UNION SELECT [ep_date] AS [reserve_date], [ep_num] FROM [reservations] WHERE [ep_date] >= date() ), reserve_max AS ( SELECT MAX(reserve_date) AS [max_date] FROM reserved_dates ), reserve_min AS ( SELECT MIN(reserve_date) AS [min_date] FROM reserved_dates ), generate_upcoming_dates(x) AS ( SELECT 0 UNION ALL SELECT x+1 FROM generate_upcoming_dates LIMIT (SELECT (((SELECT julianday(max_date) FROM reserve_max limit 1) - (SELECT julianday(min_date) FROM reserve_min limit 1))) + 1) ), upcoming_dates AS ( SELECT date((SELECT julianday(min_date) FROM reserve_min limit 1), '+' || x || ' days') as date FROM generate_upcoming_dates ) SELECT MIN(date) AS [next_open_slot] FROM upcoming_dates LEFT JOIN reserved_dates ON [reserved_dates].reserve_date = [upcoming_dates].[date] WHERE strftime('%w', date) IN ('1', '2', '3', '4','5') AND reserve_date IS NULL ```

ken_fallon commented

2024-12-27 19:31:52 +00:00

Wow that's complex

rho_n commented

2024-12-27 20:42:11 +00:00

Yes, the difficult part is generating the full range of dates. It could be simplified some by changing how the recursive function limit is generated. Instead of finding the number of days between the reserve_max and reserve_min, we could just say generate 365 days worth of dates from the current date forward. This would remove the reserve_max and reserve_min cte's.

So the WHERE clause for generate_upcoming_dates goes from:

WHERE i <= (SELECT TO_DAYS(max_date) FROM reserve_max LIMIT 1) - (SELECT TO_DAYS(min_date) FROM reserve_min LIMIT 1)

to:

WHERE i <= 365

Yes, the difficult part is generating the full range of dates. It could be simplified some by changing how the recursive function limit is generated. Instead of finding the number of days between the reserve_max and reserve_min, we could just say generate 365 days worth of dates from the current date forward. This would remove the reserve_max and reserve_min cte's. So the WHERE clause for generate_upcoming_dates goes from: ``` WHERE i <= (SELECT TO_DAYS(max_date) FROM reserve_max LIMIT 1) - (SELECT TO_DAYS(min_date) FROM reserve_min LIMIT 1) ``` to: ``` WHERE i <= 365 ```

ken_fallon commented

2024-12-28 06:33:39 +00:00

Perhaps we should try approaching it from a different angle.

Ignore the dates and just get a list of numbers from one table, and a list of numbers from another table, merge them, then find the first gap.

It might be simpler to do this in php, Or get the $first_free_slot from eps then do a loop on reservations to see if the episode number is been worked on.

Perhaps we should try approaching it from a different angle. Ignore the dates and just get a list of numbers from one table, and a list of numbers from another table, merge them, then find the first gap. It might be simpler to do this in php, Or get the `$first_free_slot` from `eps` then do a loop on `reservations` to see if the episode number is been worked on.

rho_n commented

2024-12-28 16:40:55 +00:00

Yes, doing it from PHP like that is easy enough. The SQL from the first CTE, reserved_dates, gives you the list of dates to have been taken:

	SELECT date AS 'reserve_date', id AS 'ep_num'
	FROM eps
	WHERE date >= CURRENT_DATE()
	UNION
	SELECT ep_date AS 'reserve_date', ep_num
	FROM reservations
	WHERE ep_date >= CURRENT_DATE()

Yes, doing it from PHP like that is easy enough. The SQL from the first CTE, reserved_dates, gives you the list of dates to have been taken: ``` SELECT date AS 'reserve_date', id AS 'ep_num' FROM eps WHERE date >= CURRENT_DATE() UNION SELECT ep_date AS 'reserve_date', ep_num FROM reservations WHERE ep_date >= CURRENT_DATE() ```

rho_n commented

2024-12-28 16:45:24 +00:00

I do like DB solutions as I find I end up rewriting front end code more often than DB queries, but HPR code is probably more stable than other projects I have helped--so not as much an issue.

ken_fallon commented

2024-12-28 16:47:29 +00:00

We only need the eps

SELECT DISTINCT (id) AS 'ep_num'
FROM eps
WHERE date >= CURRENT_DATE()
UNION
SELECT ep_num
FROM reservations

Can the SELECT id + 1 FROM eps mo WHERE NOT EXISTS ( SELECT NULL FROM eps mi WHERE mi.id = mo.id + 1 ) ORDER BY id LIMIT 1" be run over this list

We only need the eps ``` SELECT DISTINCT (id) AS 'ep_num' FROM eps WHERE date >= CURRENT_DATE() UNION SELECT ep_num FROM reservations ``` Can the `SELECT id + 1 FROM eps mo WHERE NOT EXISTS ( SELECT NULL FROM eps mi WHERE mi.id = mo.id + 1 ) ORDER BY id LIMIT 1"` be run over this list

norrist commented

2025-01-01 14:38:57 +00:00

select all_ids.id+1 as available_id FROM
    (SELECT id 
    FROM eps
    UNION
    select ep_num FROM  reservations)
    as all_ids
LEFT JOIN eps
ON all_ids.id+1 = eps.id
LEFT JOIN reservations r 
ON all_ids.id +1 = r.ep_num
WHERE eps.id is Null and r.ep_num is NULL
ORDER BY available_id
LIMIT 1
;

Create a union of all episode IDs from eps and reservations tables
Join the union list + 1 with the IDs from the eps and reservation tables
WHERE clause to select rows in the union list +1 that are not in eps and not in reservations
Order by and Limit to select the smallest

```sql select all_ids.id+1 as available_id FROM (SELECT id FROM eps UNION select ep_num FROM reservations) as all_ids LEFT JOIN eps ON all_ids.id+1 = eps.id LEFT JOIN reservations r ON all_ids.id +1 = r.ep_num WHERE eps.id is Null and r.ep_num is NULL ORDER BY available_id LIMIT 1 ; ``` - Create a union of all episode IDs from eps and reservations tables - Join the union list + 1 with the IDs from the eps and reservation tables - WHERE clause to select rows in the union list +1 that are not in eps and not in reservations - Order by and Limit to select the smallest

Sign in to join this conversation.

No Label

No Milestone

No project

No Assignees

3 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: HPR/hpr_hub#71