How to order by a related class?


#1

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


#2

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


#3
         :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”.