# Sorting ActiveRecords by belongs\_to fields

**URL:** <https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218>\
**Category:** Rails\
**Created:** [October 6, 2006, 4:22am UTC](https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218 "2006-10-06T04:22:15Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [October 6, 2006, 4:22am UTC](https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218/1 "2006-10-06T04:22:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [October 6, 2006, 4:58am UTC](https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218/2 "2006-10-06T04:58:23Z")

</div>

> 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](http://dev.rubyonrails.org/ticket/5851)

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [October 6, 2006, 5:06am UTC](https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218/3 "2006-10-06T05:06:12Z")

</div>

Chris H. wrote:

> 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?

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

-DJCP

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [October 6, 2006, 6:14am UTC](https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218/4 "2006-10-06T06:14:34Z")

</div>

Daniel Collis-puro wrote:

> ```
> #..... 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

---

<div class="post-metadata">

**Author:** ![bigdaddy71](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/bbe5ce/32.png) [@bigdaddy71](https://www.ruby-forum.com/u/bigdaddy71)\
**Post date:** [October 6, 2006, 6:34am UTC](https://www.ruby-forum.com/t/sorting-activerecords-by-belongs-to-fields/71218/5 "2006-10-06T06:34:35Z")

</div>

On Friday 06 October 2006 06:12, Chris H. wrote:

> 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.

: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

–  
Michael S.  
[mailto:removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
[http://www.schuerig.de/michael/](http://www.schuerig.de/michael/)