ActiveRecord Find All not sorting by ID?

Question

I've got a strange issue on a Heroku deployment that I can't seem to duplicate locally. Basically when I find all on a specific model instead of sorting by ID it seems to return them in no order at all.

Typically the records come out like so:

>> Model.all

=> [<model id: 2>,<model id: 1>,<model id: 3>,<model id: 4>,<model id: 5>]

... and so on.

If I explicitly call Model.order("id ASC") it returns the models as expected.

What gives? Why would find all not return the objects in descending ID order?

Answer 1

Ordering by ID is not guaranteed by default. It’s up to the database how a non-ordered query gets ordered (typically it’s unspecified). If you want your results to be ordered, you need to specify an explicit order with order, as you’ve done:

Model.order(id: :asc)

Note also that ordering by id should only be done if you want a deterministic order. If you want to order by time, use created_at or updated_at (nothing guarantees that ids are chronologically ordered). If you want all queries to always be ordered, you could use default_scope, but generally its use should be avoided.

Answer 2

In SQL, tables are considered to be sets of records, not lists of records, and a 'select' query is not guaranteed to return records in any particular order unless an 'order by' clause is specifically included. You may happen to see that results come back in a particular order sometimes, but that doesn't mean you can or should assume it will always be so.

Using ActiveRecord, you can force a default 'order by' clause if you like by specifying a default scope. In general, that's a bad idea though, because it will force the server to do more work to give you a sorted result set, even when you don't need it sorted. Furthermore, sorting in the 'id' field is usually inappropriate, since the point of 'id' is to be an opaque record identifier with no purpose or meaning other than to be unique for a given record in a table.

Answer 3

Just a update for Andrew's great answer (sorry I don't have enough reputation to add as a comment), the support for calling #default_scope without a block is now removed. Currently the acceptable syntax in Model is:

default_scope { sort(id: 'ASC') }

Answer 4

You should explicitly order your queries. Usually, there's no guaranteed or fixed order provided by the database.

Also, you should not use default_scope (see: 1, 2, 3). Use a normal scope or explicit ordering instead:

class Model < ApplicationRecord
  scope :oldest_first { order(created_at: :asc) }
end

Model.oldest_first.limit(10)
Model.order(created_at: :desc).limit(10)

Answer 5

Ok, for the record my tests yielded the following explanation: on PostgreSQL (and possibly others) the "all" method appears to return things in the order of their last save (see comment below). So, the most recently saved item returns last and the oldest saved item returns first. I was able to "fix" the order by resaving all the models in ID order.

This issue does not exist on SQLite etc, but Steve's answer makes sense (there's no guarantee the records will come back in a specific order). Also, Andrew Marshall's answer does work.