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.
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!
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).
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?
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?”
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.
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