On 6 December 2014 at 17:20, Dave Aronson <[email protected]> wrote: > On Sat, Dec 6, 2014 at 9:46 AM, Colin Law <[email protected]> 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 [email protected]. To post to this group, send email to [email protected]. 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.
