Rails - Jul 2010 - get last record before, or first record after, a given date?

Can anyone give me the sql for this?  I can''t quite figure it out.  I
want to find the most recent record from the states table that is before
a given date, and if that doesn''t exist, the first one *after* that
date?

Currently i''m doing it as two seperate finds, like this, but i need to
combine it into one sql call if i can.

      self.states.find(:first, :conditions => ["created_at < ?",
time],
:order => "created_at desc") ||
        self.states.find(:first, :conditions => ["created_at >
?",
time], :order => "created_at")

thanks, max
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

I think this SQL would do it:

SELECT *
FROM `states`
WHERE created_at < ''2010-04-16''
  OR created_at = (
    SELECT created_at
    FROM `states`
    ORDER BY created_at ASC
    LIMIT 1)
ORDER BY created_at DESC
LIMIT 1

But your original solution would be easier to maintain!
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Rob Nichols wrote:
> I think this SQL would do it:
> 
> SELECT *
> FROM `states`
> WHERE created_at < ''2010-04-16''
>   OR created_at = (
>     SELECT created_at
>     FROM `states`
>     ORDER BY created_at ASC
>     LIMIT 1)
> ORDER BY created_at DESC
> LIMIT 1
> 
> But your original solution would be easier to maintain!
Thanks rob. It didn''t occur to me that my fallback option will always 
just be the one with the lowest created_at, rather than the one after a 
given time (since we already established that there aren''t any before 
the time with the first option).

cheers, max
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 14 July 2010 15:22, Max Williams
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Can anyone give me the sql for this?  I can''t quite figure it out.
 I
> want to find the most recent record from the states table that is before
> a given date, and if that doesn''t exist, the first one *after*
that
> date?
Do you mean date or datetime?  In other words what about about any on
the given date?
If you mean datetime then there could still be one exactly at that
time.  Your spec says that this one should not be found, is that
correct?

Colin
>
> Currently i''m doing it as two seperate finds, like this, but i
need to
> combine it into one sql call if i can.
>
>      self.states.find(:first, :conditions => ["created_at <
?", time],
> :order => "created_at desc") ||
>        self.states.find(:first, :conditions => ["created_at >
?",
> time], :order => "created_at")
>
> thanks, max
> --
> Posted via http://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Hi colin - yes, sorry that was badly worded.  Rob''s solution is fine
but
for the record i meant ''datetime'' and i should have said

"I want to find the most recent record from the states table before a 
given datetime, and if that doesn''t exist, the first one *after* (or
at)
that datetime?"

cheers, max
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 14 July 2010 16:44, Max Williams
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> Rob Nichols wrote:
>> I think this SQL would do it:
>>
>> SELECT *
>> FROM `states`
>> WHERE created_at < ''2010-04-16''
>>   OR created_at = (
>>     SELECT created_at
>>     FROM `states`
>>     ORDER BY created_at ASC
>>     LIMIT 1)
>> ORDER BY created_at DESC
>> LIMIT 1
>>
>> But your original solution would be easier to maintain!
>
> Thanks rob. It didn''t occur to me that my fallback option will
always
> just be the one with the lowest created_at, rather than the one after a
> given time (since we already established that there aren''t any
before
> the time with the first option).
Is there any advantage to this rather than the original solution (now
simplified by the realisation that the second query does not need the
:conditions spec)?  It is all done in one query, but both parts of the
query will be executed even when the second part is not required, I
think.

Colin

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Colin Law wrote:
> Is there any advantage to this rather than the original solution (now
> simplified by the realisation that the second query does not need the
> :conditions spec)?  It is all done in one query, but both parts of the
> query will be executed even when the second part is not required, I
> think.
> 
> Colin
In my experience, SQL is always faster than Ruby. If you need to improve 
the performance of your Ruby application one easy way is to do fewer SQL 
calls and to get the SQL engine to do more of the work. One complex SQL 
call is almost always quicker than two or three simpler calls.

I expect the difference in performance between doing the one complex SQL 
call and just the first of Max''s original statements (the case where 
that one returns a result, and therefore the second ''or''
statement isn''t
called), is minimal. Unless your SQL call is particularly complex or 
poorly designed, its not SQL that slows the data retrieval in 
ActiveRecord.

However, the trade off is maintainability. Two or three simple steps are 
usually easier to write and easier to debug. They are also easier for a 
third party to understand.

So now it''s Max''s choice - best performance or more easily
maintained.
Personally, I''d go for his original solution, unless there was a 
performance issue.

Oh! and to maximise the maintainability, I''d recommend splitting the 
queries out to their own methods:

def last_state
  self.states.find(:first, :order => "created_at")
end

def first_state_after(time)
  self.states.find(:first, :conditions => ["created_at < ?",
time],
:order => "created_at desc")
end

first_state_after(time) || last_state

-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On 15 July 2010 08:50, Rob Nichols
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
>...
> Oh! and to maximise the maintainability, I''d recommend splitting
the
> queries out to their own methods:
>
> def last_state
>  self.states.find(:first, :order => "created_at")
> end
>
> def first_state_after(time)
>  self.states.find(:first, :conditions => ["created_at < ?",
time],
> :order => "created_at desc")
> end
>
> first_state_after(time) || last_state
Or possibly even better as named_scopes (and I think a typo on the
names, they should be first_state, and last_state_before).

Colin
Colin

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

Colin Law wrote:
> even better as named_scopes (and I think a typo on the
> names, they should be first_state, and last_state_before).
Agreed on both points :o)
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.