Forum: Ruby on Rails how to order by a related class???

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
B072fc2897196b3bafcd337bc9c91e2d?d=identicon&s=25 Sas Sas (opensas)
on 2006-04-11 10:24
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
B45eab4f18aa1bb2a44d6e657531a642?d=identicon&s=25 Alain Ravet (aravet)
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
B072fc2897196b3bafcd337bc9c91e2d?d=identicon&s=25 Sas Sas (opensas)
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".
This topic is locked and can not be replied to.