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

[Max Williams]

I don't think "SELECT" is the problem e as any season (matching the basic
conditions like which town it's in or whatever) could potentially be
returned, if no other seasons exist.  I think it's an ORDER issue: you want
to order by "proximity to now", preferring current, then future things then
past things, and then once they're ordered properly you can just take the
first one.  (the ordering will also allow you to show sensible alternatives
to the user).

This is hacky but i could imagine a custom function that returns eg "0" for
current things, "f14" for something that starts 14 days in the future and
"p27" for something that finished 27 days in the past.  Doing a simple
string ordering on this result would (i think) put the results in the order
you want.  (partly just because f comes before p, and numbers come before
letters, which is a bit icky but should work).  The function will just have
a bit of simple logic around start_date and end_date which tends to be a
bit arsey inside DBMS functions but does at least neatly encapsulate the
logic.

On 29 September 2016 at 11:31, Duncan Stuart <dgmstuart at gmail.com> wrote:

> 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
>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> Archives: http://lists.lrug.org/pipermail/chat-lrug.org
> Manage your subscription: http://lists.lrug.org/options.cgi/chat-lrug.org
> List info: http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20160929/27f2f167/attachment-0002.html>