<div dir="ltr">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).<div><br></div><div>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.</div></div><div class="gmail_extra"><br><div class="gmail_quote">On 29 September 2016 at 11:31, Duncan Stuart <span dir="ltr"><<a href="mailto:dgmstuart@gmail.com" target="_blank">dgmstuart@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div>Hello LRUG</div><div><br></div><div>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: </div><div><br></div><div><b>TL;DR:</b></div><div><br></div><div>Given a set of date ranges, return either</div><div> a) the one that Today lies within </div><div> b) if no such range exists, then the next range in the future</div><div> c) if no such range exists, then the last range in the past</div><div><br></div><div> </div><div><b>Problem: </b></div><div><br></div><div>Say it's a site for listing details of club nights, past, present and future.</div><div><br></div><div>- Club nights happen at venues, and change venues pretty regularly. </div><div>- There are often periods between venue changes when a night isn't running at all. </div><div>- I need to be able to both schedule in future changes of venue, and record past venues.</div><div>- I need to display a list of club nights with their venue name - even if they're not currently running:</div><div><br></div><div>eg.</div><div>Club A - Venue A (currently running)</div><div>Club B - Venue B (closed on 01/02/2016)</div><div>Club C - Venue C (opens on 10/11/2016)</div><div>Club D - Venue D (reopens on 17/12/2016)</div><div><br></div><div>The rules on which venue to display on a particular day are:</div><div>- If the club is currently running, use the venue from the current season</div><div>- If not:</div><div> - If there are no future seasons planned, use the most recent one</div><div> - If there are future seasons planned, use the next one</div><div><br></div><div>My current database setup uses Postgres with 3 tables:</div><div>- club_night (name), has many seasons</div><div>- venue (name, address)</div><div>- season (start_date, end_date) belongs to a club night and has one venue</div><div><br></div><div>I'm struggling to represent those 3 rules in a single query.</div><div><br></div><div>I have a couple of ideas, all of which seem bad:</div><div>1. Write some SQL using `EXISTS` and conditionals to cover the 3 different cases</div><div>2. Fill in the gaps between seasons with 'not running' records with the right venue, so that all dates fall within some season</div><div>3. Load everything into ruby and work it all out with selects</div><div><br></div><div>Everything is up for grabs, including the data model, data store, software versions etc.<br><br>Any ideas gratefully received!<span class="HOEnZb"><font color="#888888"><br><br>Duncan</font></span></div></div>
<br>______________________________<wbr>_________________<br>
Chat mailing list<br>
<a href="mailto:Chat@lists.lrug.org">Chat@lists.lrug.org</a><br>
Archives: <a href="http://lists.lrug.org/pipermail/chat-lrug.org" rel="noreferrer" target="_blank">http://lists.lrug.org/<wbr>pipermail/chat-lrug.org</a><br>
Manage your subscription: <a href="http://lists.lrug.org/options.cgi/chat-lrug.org" rel="noreferrer" target="_blank">http://lists.lrug.org/options.<wbr>cgi/chat-lrug.org</a><br>
List info: <a href="http://lists.lrug.org/listinfo.cgi/chat-lrug.org" rel="noreferrer" target="_blank">http://lists.lrug.org/<wbr>listinfo.cgi/chat-lrug.org</a><br>
<br></blockquote></div><br></div>