Rails - Apr 2006 - how to order by a related class???

Let''s suppose I have a books table and an authors table.

A book belongs to an author (to put things simply)

I want to browse the book list order by the author''s name.

In sql it would be like this (to make it clear)

select
b.id,
b.title,
a.id as authors_id
a.name as authors_name
from
books as b inner join authors as a on b.authors_id = a.id
order by a.name

the problem is that rails generates something like this

SELECT * FROM (
  SELECT TOP 10 * FROM (
    SELECT TOP 10 * FROM books ORDER BY title
  ) AS tmp1 ORDER BY title desc
) AS tmp2 ORDER BY title

see? no trace of the authors table, so it''s impossible to order by any 
author''s column.

if I tell rails to include the authors table with something like

    @book_pages, @books       paginate :book,
      :order => ''title'', :per_page => 10,
      :include => [:author]

then rails generates the following

SELECT * FROM (
  SELECT TOP 10 * FROM (
    SELECT TOP 10
    books.[id] AS t0_r0,
    books.[title] as t0_r1,
    authors.[id] as t1_r0,
    authors.[name] as t1_r1
  FROM
    books LEFT OUTER JOIN authors ON books.author_id = authors.id
  ORDER BY name )
AS tmp1 ORDER BY name desc)
AS tmp2 ORDER BY name

The inner "ORDER BY NAME" can be acomplished, provided there are no 
ambigueties (that is, the table books hasn''t any column named
"name")

But then, in the outers select, there is no name column to order by.

Is this a bug, or there''s some other way to acomplish this thing???

I guess rails should generate some meaningfull field_name (like 
authors_name) and then allow us to order by book.author.name and map the 
whole thing.

Saludos

Sas



-- 
Posted via http://www.ruby-forum.com/.

sas

:include and :order are your friends:

Example: fetch all cities, by country name, and then by city name:

   City.find(:all,
             :include => ''country'',
             :order => ''countries.name, cities.name'')


Alain

-- 
Posted via http://www.ruby-forum.com/.

>              :order => ''countries.name, cities.name'')
Thanks a lot Alain, it worked like a charm.

BTW, where is it documented?

the rails documentation only says


http://api.rubyonrails.com/, find (ActiveRecord::Base)

:order: An SQL fragment like "created_at DESC, name".


-- 
Posted via http://www.ruby-forum.com/.