Be careful using default_scope and order()

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.

 

 

 

Comments

[Nzkoz]

Generally speaking, my advice is never ever ever use default_scope. No, not even then.

[Sbfaulkner]

A default_scope for ignoring logically deleted records is the only use case I've found that hasn't bit me in the a** at some point

[A_matsuda]

Agree that default_scope + order smells.
BTW we have AR::Relation#reorder method for this case
https://github.com/rails/rails/blob/ded74df/activerecord/lib/active_record/relation/query_methods.rb#L120

Wish.reorder("created_at desc").limit(3)

[Coffeeaddict]

I wish SQL would just die