YANQ (Yet Another Newbie Question): Ordered entries in a database

rubyonrails-talk
1

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.

--wpd

2

See: <http://ryandaigle.com/articles/2008/11/18/what-s-new-in-edge-rails-default-scoping&gt;

3

Thanks! That's just what I was l looking for.

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.

--wpd

4

Thanks! That's just what I was l looking for.

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.

It is.

5

And, your database should have an index to match the :order clause of your default_scope

So if you have a Person model and you always want to get them by :order => 'last_name, first_name'

Then the database ought to have an index, too. In a migration it would look something like:

class AddPeopleNameIndex < ActiveRecord::Migration def self.up    add_index :people, [:last_name, :first_name], :name => 'index_people_by_full_name' end def self.down    remove_index :people, :name => 'index_people_by_full_name' end

(But if you didn't know about database indexes until now, get yourself to some friendly neighborhood documentation ASAP!)

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com

6

Is there a non-standard way to do this specific to Sqlite3, MySQL, etc... ?

You can use a view:

http://dev.mysql.com/doc/refman/5.0/en/create-view.html

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:

http://activewarehouse.rubyforge.org/rails_sql_views/

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).

HTH!

7

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...

--wpd

8

So you can add an :order clause to the has_many relationship:

class Parent    has_many :children, :order => 'date_of_birth' end

-Rob

Rob Biedenharn http://agileconsultingllc.com Rob@AgileConsultingLLC.com