Finding records either side of a reference

rubyonrails-talk
1

I have a simple problem that I am sure has a simple answer, but my brain will not produce the answer. I have a table (segments) that has a start_time field. Given a specific time 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.

Any suggestions gratefully received.

Colin

2

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

-Dave

3

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

Yes, ActiveRecord does exactly that.

Thanks

Colin