I just switched to Postgres and some of my tests are failing. It seems
that Postgres defaults by ordering data by ID DESC, whereas MySQL
defaults to ordering by ID ASC.
What is the best practice for handling this? Is there a setting in
Postgres you can change? Should I be explicitly specifying the order of
my data in my queries?
In various places throughout my application I am doing things like:
User.first
This obviously is not good with the order of the data is not consistent.
I also feel like the first User should return the first user, not the
last one created.
What do you think?
On Tue, Aug 18, 2009 at 5:01 PM, Ben
Johnson[email protected] wrote:
User.first
This obviously is not good with the order of the data is not consistent.
I also feel like the first User should return the first user, not the
last one created.
The Rails tests use explicit ordering to work around this discrepancy.
User.first without an ordering is technically undefined. It could be any
row.
Since you’re usually doing account.users.first, you could :order =>
‘id asc’ on the has_many.
jeremy
Ben J. wrote:
I just switched to Postgres and some of my tests are failing. It seems
that Postgres defaults by ordering data by ID DESC, whereas MySQL
defaults to ordering by ID ASC.
As Jeremy correctly pointed out, without an ORDER clause, your records
may be returned in any order.
[…]
In various places throughout my application I am doing things like:
User.first
This obviously is not good with the order of the data is not consistent.
I also feel like the first User should return the first user, not the
last one created.
There is no guarantee of that – without the ORDER clause, the database
is free to return the first user created, the last user created, or one
somewhere in the middle. User.first just returns whichever user the DB
decided to retrieve first. If you need a specific ordering, you need an
order clause.
What do you think?
I think you don’t quite understand how SQL databases retrieve records.
Best,
Marnen Laibow-Koser
http://www.marnen.org
[email protected]
(My point of view) Order the result by ID is also a bad idea, if you
switch to another ID schema (for example UUID, descending sequence,
etc.) you must rewrite a lot of code. The ID is something the
application must not “know”, order for example by name, by a
created_at field (and that have more sense if you want “the last
created user”).
Regards.
Franco C…
On Aug 18, 9:01 pm, Ben J. [email protected]
Marnen Laibow-Koser wrote:
What do you think?
I think you don’t quite understand how SQL databases retrieve records.
Think of it this way: select * from users; returns a record “set.” As
in, “unordered set” as apposed to a record “array.” An id field is just
another column that happens to be used to identify rows. The only
requirement (assuming id is used as a primary key) is that each value is
unique. The sequence of those values is undefined. Therefore, the order
of the record set is undefined.
An “order by” clause is applied to the resulting set before the set is
returned, which essentially turns it into an “ordered set/array.” This
final result is stored in an Array object by ActiveRecord (another
ordered storage object) so can be depended upon to be kept in order.