In a recent post, I talked about how the Rails first and limit/offset modifiers to queries are not specified to return a particular result—databases are free to determine the order of rows in a result set if the query doesn't contain an explicit order by clause, so the actual row(s) returned by these methods may not be determined.
Some folks responded (in comments, tweets, and emails) by suggesting that you can easily solve this problem by adding a default_scope call at the top of models that you use first and offset on. Ignoring the fact that this really isn't a solution (both the names first and offset imply an ordering which they don't deliver, and the fix should be in them); ignoring that, the suggestion might lead to another unexpected problem.
Lets start with a simple ActiveRecord model:
class Wish
end
Executing Wish.limit(10).offset(10).to_sql returns the SQL
SELECT `wishes`.* FROM `wishes` LIMIT 10 OFFSET 10
Noticing the lack of an order by clause, you add a default scope:
class Wish
default_scope order(:id)
end
Now when we execute the query, the SQL contains an order by clause:
SELECT `wishes`.* FROM `wishes` ORDER BY id LIMIT 10 OFFSET 10
Cool. Problem fixed.
However, the user also wants a list of the 3 most recent wishes, so you code
Wish.order("created_at desc").limit(3)
Imagine your chagrin when you look at the SQL that gets run and see
SELECT `wishes`.* FROM `wishes` ORDER BY id, created_at desc LIMIT 3
Because the id column is unique, it totally determines the ordering—the created_at desc part of the query has no effect.
Once you add a default_scope with an order clause to a model, all subsequent finders on that model will have that order as their primary ordering. If you want some other order, you'll need to remember to add unscoped to the chain:
Wish.unscoped.order("created_at desc").limit(3)
And that's an accident waiting to happen.
