Sorting ActiveRecords by belongs_to fields

rubyonrails-talk
1

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

2

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

3

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

4

: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