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
on 2006-04-11 10:24
on 2006-04-11 16:24
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
on 2006-04-11 17:56
> :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".