Why is Foo.first returning the last record?

activerecord, postgresql, ruby-on-rails, ruby-on-rails-3

Solution

There isn't any logic to it, if there was any sense to `first` (or `last` for that matter), then it would raise an exception if you neglected to specify an explicit order either as an argument to `first` or as part of the current scope chain. Neither `first` nor `last` make any sense whatsoever in the context of a relational database unless there is an explicit ordering specified.

My guess is that whoever wrote `first` assumed that `order by whatever_the_pk_is` was implicit if there was no explicit `order by`. Then they probably did some experiments to empirically verify their assumption and it just happened to work as they expected with the particular tables and databases that they checked with (mini-rant: this is why you never ever assume unspecified behavior; if a particular behavior isn't explicitly specified, don't assume it even if the current implementation behaves that way or if empirical evidence suggests that it behaves that way).

If you trace through a simple `M.first`, you'll find that it does this:

limit(1).to_a[0]

No explicit ordering so you get whatever random ordering the database feels like using, that could be `order by pk` or it could be the table's block order on disk. If you trace through `M.last`, you'll get to `find_last`:

def find_last
  #...
        reverse_order.limit(1).to_a[0]
  #...
end

And `reverse_order`:

def reverse_order
  relation = clone
  relation.reverse_order_value = !relation.reverse_order_value
  relation
end

The `@reverse_order_value` instance variable isn't initialized so it will start out as `nil` and a `!` will turn it into a `true`. And if you poke around for how `@reverse_order_value` is used, you'll get to `reverse_sql_order`:

def reverse_sql_order(order_query)
  order_query = ["#{quoted_table_name}.#{quoted_primary_key} ASC"] if order_query.empty?
  #...

and there's the author's invalid assumption about ordering laid bare for all to see. That line should probably be:

raise 'Specify an order you foolish person!' if order_query.empty?

I'd recommend that you always use `.order(...).limit(1).first` instead of `first` or `last` so that everything is nice and explicit; of course, if you wanted `last` you'd reverse the `.order` condition. Or you could always say `.first(:order => :whatever)` and `.last(:order => :whatever)` to again make everything explicit.

Problem

I have 2 records in `Foo`, with id's 1 and 2. Both created in that order. Bare in mind, in Postgres, records have no inherent order. In Rails console. `Foo.first` and `Foo.last` returns the last record. I was under the impression that `Foo.first` would return the first record. Here's the catch. The SQL queries look like: ``` SELECT "foos".* FROM "foos" LIMIT 1 SELECT "foos".* FROM "foos" ORDER BY "foos"."id" DESC LIMIT 1 ``` The second query (`Foo.last`) has an `ORDER BY DESC`. So why doesn't AR have an `ORDER BY ASC` for `.first`? Whats the logic behind this? Seems a bit "inconsistent". I can easily solve this by doing: `Foo.order('id ASC').first` instead. But looking for an explanation.

Original source