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

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 2

Ordering by ID is not guaranteed by default, since it’s up to the database how a non-ordered query gets ordered (typically it’s unspecified). You can make it always be ordered by defining a default scope at the top of your model like so:

default_scope { order('id ASC') }

Then calling Model.all will be equivalent to calling Model.order('id ASC').

Answer 3

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.

Answer 4

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.