Rails - Oct 2009 - YANQ (Yet Another Newbie Question): Ordered entries in a database

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

On Thu, Oct 8, 2009 at 4:50 PM, Patrick Doyle
<wpdster-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> Is there a standard (or better) Rails way to indicate the same thing?
See:
<http://ryandaigle.com/articles/2008/11/18/what-s-new-in-edge-rails-default-scoping>

-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
twitter: @hassan

On Thu, Oct 8, 2009 at 7:59 PM, Hassan Schroeder
<hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Thu, Oct 8, 2009 at 4:50 PM, Patrick Doyle
<wpdster-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> Is there a standard (or better) Rails way to indicate the same thing?
>
> See:
>
<http://ryandaigle.com/articles/2008/11/18/what-s-new-in-edge-rails-default-scoping>
>
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

On Thu, Oct 8, 2009 at 5:05 PM, Patrick Doyle
<wpdster-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 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.

-- 
Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
twitter: @hassan

On Oct 8, 2009, at 8:16 PM, Hassan Schroeder wrote:
> On Thu, Oct 8, 2009 at 5:05 PM, Patrick Doyle
<wpdster-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
>> 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.
>
> -- 
> Hassan Schroeder ------------------------
hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
> twitter: @hassan
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
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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org

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

On Fri, Oct 9, 2009 at 9:01 AM, Billee D.
<william.dodson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>> 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
>
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

On Oct 9, 2009, at 9:31 AM, Patrick Doyle wrote:
> On Fri, Oct 9, 2009 at 9:01 AM, Billee D.
<william.dodson-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>>> 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
>>
> 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

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-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org