Sorting ActiveRecords by belongs_to fields

Hi folks,

I'm new to Rails, so apologies if this is ignorant.

Suppose I have a User model, and a Friend model which is a many-to-many
User-to-User relationship. How might I retrieve an array of a given
user's friends, sorted by user name?

I'm retrieving an array of Friends from a Friend model, and using
belongs_to to access the User model. Does a find allow me sort by a
column on a linked table? I want to retrieve an array of friends and
not just users, as there are properties on the relationship that are
important to my Friend model.

I'm not even sure that this is correct place to do my sorting, but I
think the DB can handle sorting much faster than the application code.

Essentially, I want to do the equivalent of:

SELECT friends.*, users.*
FROM friends INNER JOIN users ON users.id = friends.user_id
ORDER BY users.name

Any guidance would be appreciated.

Thanks,
Chris

I'm new to Rails, so apologies if this is ignorant.

Suppose I have a User model, and a Friend model which is a many-to-many
User-to-User relationship. How might I retrieve an array of a given
user's friends, sorted by user name?

I'm retrieving an array of Friends from a Friend model, and using
belongs_to to access the User model. Does a find allow me sort by a
column on a linked table? I want to retrieve an array of friends and
not just users, as there are properties on the relationship that are
important to my Friend model.

I'm not even sure that this is correct place to do my sorting, but I
think the DB can handle sorting much faster than the application code.

Essentially, I want to do the equivalent of:

SELECT friends.*, users.*
FROM friends INNER JOIN users ON users.id = friends.user_id
ORDER BY users.name

Yes, you can, but beware there are some issues if you try and limit the result... See this bug for both an example of how to do what you want and the problems you might encounter...

http://dev.rubyonrails.org/ticket/5851

Daniel Collis-puro wrote:

> Chris Hughes wrote:
>
> Suppose I have a User model, and a Friend model which is a many-to-many
> User-to-User relationship. How might I retrieve an array of a given
> user's friends, sorted by user name?

Maybe I'm not understanding the question, but the simplest answer would
be:

class User < ActiveRecord::Base
    #..... stuff
    has_and_belong_to_many :friends, :order=>:username
    #..... more stuff
end

The more general problem that I didn't describe well was how to sort a
query on a model by a field that is not in its model. Philip's link
looks like it addresses that problem.

But your response prompted me to rethink my design. I wanted to query
by the Friendship model, because there are other fields in that model
that are important. But if I query the friendship and take the username
from the linked user, there are actually 2 links (a friendship involves
2 people) and I would have to query for them separately and then merge
the results to preserve the sort.

Thanks for your help!

Also, for anyone still reading, the equivalent DB query is actually
more like the following:

SELECT friends.*, users.name
FROM friends INNER JOIN users ON users.id = friends.user1_id
ORDER BY users.name
UNION
SELECT friends.*, users.name
FROM friends INNER JOIN users ON users.id = friends.user2_id
ORDER BY users.name

Cheers,
Chris

:include the association in question in the find options. Then look what
alias, if any, ActiveRecord generates for the column you want to sort
by. Add an :order option for the column, possibly qualified by table
name, to the find options.

Michael