Ruby on Rails

On Sat, Dec 6, 2014 at 9:46 AM, Colin Law <clanlaw@gmail.com> wrote:

> I want to find the last segment with a start_time <=
> time and the first one with start_time > time, so
> Segment.order(:start_time).where( "start_time <= ?", time).last
> and
> Segment.order(:start_time).where( "start_time > ?", time).first
> I feel sure it must be possible to do this in one query, but I can't see how.

Nothing leaps to mind re doing it in one query, given your
requirements. Depending how much flexibility you have, it might be
close enough to order by absolute value of difference between
start_time and your desired time, and take the first two.

If you really do need the first before or matching, and the first
strictly after, maybe you can make those two queries more efficient,
which I would figure is probably your overall goal in this question.
I'm not sure if the database will send Rails *all* the matching
records and let Rails apply the .last and .first. If it does, then
using .limit(1) like this should make the DB do the filtering before
sending the records back to Rails:

first_before_or_it = Segment.where( "start_time <= ?", time).
order(:start_time => :desc).limit(1).last
first_after_it = Segment.where( "start_time > ?", time).
order(:start_time => :asc).limit(1).last

(Note the opposite ordering.) Ideally one would think ActiveRecord
would apply the .last and .first do do the limiting for you in the
database, but I can't be bothered to go check right now. :-)

-Dave

--
Dave Aronson, consulting software developer of Codosaur.us,
PullRequestRoulette.com, Blog.Codosaur.us, and Dare2XL.com.

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/CAHxKQig0-PnY6uH6YG%3DDFCVbW76cZuKJkCgFn2kAHAYp_c%2B5Zw%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.