Finding records either side of a reference

Rails
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

On Sat, Dec 6, 2014 at 9:46 AM, Colin L. [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.

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

–
Dave A., consulting software developer of Codosaur.us,
PullRequestRoulette.com, Blog.Codosaur.us, and Dare2XL.com.

3

On 6 December 2014 at 17:20, Dave A.
[email protected] wrote:

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