Ruby on Rails

On 6 December 2014 at 17:20, Dave Aronson
<googlegroups.2.TRex@codosaur.us> wrote:
> 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.

I think you may be right, that there is sensible way to do it in one
query. I can't use the abs technique as the time stamps are
essentially random so that might give me two samples before or after
rather than surrounding the event.

>
> 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:

In fact there is no great need to change the code I have, it just
offends me having to have the two similar queries one after the other,
so it is purely an aesthetic issue really. Often when I see code like
that it means I am not doing it the best way, but perhaps not in this
case. I suppose it comes down to the fact that in sql one cannot say
"give me the records starting with the last one that meets this
criterion".

>
> 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. :-)

Yes, ActiveRecord does exactly that.

Thanks

Colin

--
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/CAL%3D0gLsVeBRnqFm5Foce9MxCLxkNJnjgWO1Q4%3DgetgD7DiCvhA%40mail.gmail.com.
For more options, visit https://groups.google.com/d/optout.