[LRUG] Finding the most relevant date range from a set of database records

[Duncan Stuart]

Hello LRUG

I have a Rails 5 app in development and I've run into a problem around
database queries/ranges/dates that some of you might find interesting:

*TL;DR:*

Given a set of date ranges, return either
 a) the one that Today lies within
 b) if no such range exists, then the next range in the future
 c) if no such range exists, then the last range in the past


*Problem: *

Say it's a site for listing details of club nights, past, present and
future.

- Club nights happen at venues, and change venues pretty regularly.
- There are often periods between venue changes when a night isn't running
at all.
- I need to be able to both schedule in future changes of venue, and record
past venues.
- I need to display a list of club nights with their venue name - even if
they're not currently running:

eg.
Club A - Venue A (currently running)
Club B - Venue B (closed on 01/02/2016)
Club C - Venue C (opens on 10/11/2016)
Club D - Venue D (reopens on 17/12/2016)

The rules on which venue to display on a particular day are:
- If the club is currently running, use the venue from the current season
- If not:
  - If there are no future seasons planned, use the most recent one
  - If there are future seasons planned, use the next one

My current database setup uses Postgres with 3 tables:
- club_night (name), has many seasons
- venue (name, address)
- season (start_date, end_date) belongs to a club night and has one venue

I'm struggling to represent those 3 rules in a single query.

I have a couple of ideas, all of which seem bad:
1. Write some SQL using `EXISTS` and conditionals to cover the 3 different
cases
2. Fill in the gaps between seasons with 'not running' records with the
right venue, so that all dates fall within some season
3. Load everything into ruby and work it all out with selects

Everything is up for grabs, including the data model, data store, software
versions etc.

Any ideas gratefully received!

Duncan
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20160929/be84d00d/attachment.html>