I know this is kinda basic, but who else could I ask besides you folks?
Is there a standard SQL way to indicate that the records in a database
should always be returned sorted by a particular column?
Is there a standard (or better) Rails way to indicate the same thing?
Is there a non-standard way to do this specific to Sqlite3, MySQL,
etc… ?
Yes, I know I can do a:
parts = Part.find(:order => :number)
whenever I want to retrieve a list of parts from my db, but the spirit
of DRY doesn’t seem to be appeased when I find myself writing this everywhere I fetch parts.
I suppose I could write a specific class method for my Part model, or
perhaps I could even override the default #find method, but I started
wondering if, perhaps, there was a standard SQL way, standard Rails
way, or nonstandard db-specific way, to indicate that I always want
records returned from my Parts table to be sorted by part number.
I still wonder if there is some way to optimize this at the database
– If I know that I’m always going to return records sorted in a
certain order, perhaps the database engine could do something to
optimize for this. I have heard the term “index” used in the context
of database engines… perhaps I should look into what that means and
whether or not it is the sort of magic that is supposed to apply to
this situation.
In the mean time, I’ll start playing with #default_scope, assuming
it’s present in 2.3.4.
This is supported with slightly varying syntax by Oracle, MSSQL,
MySQL, and PostgreSQL. I can’t remember if AR support for views has
been added to any newer version of Rails yet, but here is a gem I have
used in the past:
There are some caveats and gotchas, but as long as your query is read-
only data you should be fine. Another option is to hack the Rails core
MySQL (or database-specific) adapter and trick AR into seeing views as
tables (though I wouldn’t suggest this for any production code).
From what I’ve read, using #default_scope and adding an index to my db
seem to be the way to go. I was looking for some way to tell the db,
“BTW, whenever I grab records from this table, I’m going to want them
sorted by the ‘number’ field, so if there’s anything you might want to
do to make that operation go more quickly, you should probably do
that.” It seems that adding an index for that field tells the db
engine that there is something special about that field and that it
might want to optimize things to speed up searches (and ultimately
sorts) on that field. Once I’ve told the db engine about that, then
telling Rails that the default scope should be to sort by that field,
I’ve gotten where I wanted to get.
Some simple testing shows that the default_scope notion doesn’t
propagate to #has_many relationships. For example, if I wanted to
fetch all of my lot records, sorted by the part numbers, then I still
need to specify a sort order if I want the results to be sorted by
part number. Not too surprising…
I still wonder if there is some way to optimize this at the database
… I have heard the term “index” used in the context
of database engines… perhaps I should look into what that means and
whether or not it is the sort of magic that is supposed to apply to
this situation.
No, it’s not. An RDBMS has no intrinsic sort order, so if what you’re
looking for exists at all, it’ll be a vendor-specific extension.
In the mean time, I’ll start playing with #default_scope, assuming
it’s present in 2.3.4.
seem to be the way to go. I was looking for some way to tell the db,
Some simple testing shows that the default_scope notion doesn’t
propagate to #has_many relationships. For example, if I wanted to
fetch all of my lot records, sorted by the part numbers, then I still
need to specify a sort order if I want the results to be sorted by
part number. Not too surprising…
–wpd
So you can add an :order clause to the has_many relationship:
class Parent
has_many :children, :order => ‘date_of_birth’
end