# :join not working **URL:** \ **Category:** Rails\ **Created:** [May 24, 2007, 2:11am UTC](https://www.ruby-forum.com/t/join-not-working/104935 "2007-05-24T02:11:27Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![dgm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/df788c/32.png) [@dgm](https://www.ruby-forum.com/u/dgm)\ **Post date:** [May 24, 2007, 2:11am UTC](https://www.ruby-forum.com/t/join-not-working/104935/1 "2007-05-24T02:11:27Z")
I have a table called emails and a table called codes. emails have columns email\_addr and type\_cd and codes have columns family\_name, code, code\_desc and rank. For example: –emails– email\_addr : removed\_email\_address@domain.invalid type\_cd : HM –codes– family\_name : emails code : HM code\_desc : Home rank : 1 so in this example I have an email address with a type code of HM which relates to the codes table column code meaning that my email address “removed\_email\_address@domain.invalid” is in the code family of emails, it is my “Home” email address and the Home email addresses are ranked 1 according to the codes table (as opposed to, for example the work email address my be ranked 2 in the codes table). I am trying to return all the emails for a particular individual and order them by rank. I can easily return all emails for an individual like so: individual.emails But I need to order these emails by the rank. The catch is that the rank is on the codes table. I tried something like this: ``` return individual.emails.find( :all, :joins => "as e inner join codes as c on e.type_cd = c.type_cd", :order => "c.rank" ) ``` It doesn’t seem to pick up my join. (Also to make things more complicated individuals is joined to emails through a join table because organizations also have emails.) So I end up getting this error: Mysql::Error: Unknown column ‘c.rank’ in ‘order clause’: SELECT \* FROM emails INNER JOIN emails\_individuals ON emails.id = emails\_individuals.email\_id WHERE (emails\_individuals.individual\_id = 1 ) ORDER BY c.rank My join is not in the sql??? thanks ---
**Author:** ![dgm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/df788c/32.png) [@dgm](https://www.ruby-forum.com/u/dgm)\ **Post date:** [May 24, 2007, 2:22am UTC](https://www.ruby-forum.com/t/join-not-working/104935/2 "2007-05-24T02:22:03Z")
It’s sort of a bug in ActiveRecord. Sort of because there are certain situations where joins are dropped (:include with :limit being another), but I don’t think they need to be. On one hand it does prevent name clashes, and there might even be pure ActiveRecord scenarios that break if they don’t do it that way. I haven’t really thought it all through enough, but I have gone as far as to monkey patch the eager loading code to allow me to do that. However in this case I don’t have a patch. I have worked around it by just adding the conditions :individual\_id =\> individual.id on a base Email.find. Ugly I know, but necessary, at least for now. David M. wrote: > I have a table called emails and a table called codes. emails have > columns email\_addr and type\_cd and codes have columns family\_name, code, > code\_desc and rank. For example: > > –emails– > email\_addr : removed\_email\_address@domain.invalid > type\_cd : HM > > –codes– > family\_name : emails > code : HM > code\_desc : Home > rank : 1 > > so in this example I have an email address with a type code of HM which > relates to the codes table column code meaning that my email address > “removed\_email\_address@domain.invalid” is in the code family of emails, it is my “Home” > email address and the Home email addresses are ranked 1 according to the > codes table (as opposed to, for example the work email address my be > ranked 2 in the codes table). > > I am trying to return all the emails for a particular individual and > order them by rank. I can easily return all emails for an individual > like so: > > individual.emails > > But I need to order these emails by the rank. The catch is that the > rank is on the codes table. > > I tried something like this: > > ``` > return individual.emails.find( > :all, > :joins => "as e inner join codes as c on e.type_cd = c.type_cd", > :order => "c.rank" > ) > > ``` > > It doesn’t seem to pick up my join. (Also to make things more > complicated individuals is joined to emails through a join table because > organizations also have emails.) > > So I end up getting this error: > > Mysql::Error: Unknown column ‘c.rank’ in ‘order clause’: SELECT \* FROM > emails INNER JOIN emails\_individuals ON emails.id = > emails\_individuals.email\_id WHERE (emails\_individuals.individual\_id = 1 > ) ORDER BY c.rank > > My join is not in the sql??? > > thanks ---
**Author:** ![dgm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/df788c/32.png) [@dgm](https://www.ruby-forum.com/u/dgm)\ **Post date:** [May 24, 2007, 2:42am UTC](https://www.ruby-forum.com/t/join-not-working/104935/3 "2007-05-24T02:42:00Z")
I’m not following. How does this help me considering I want to order by rank which is not on the emails but it is on the codes table. ---
**Author:** ![dgm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/d/df788c/32.png) [@dgm](https://www.ruby-forum.com/u/dgm)\ **Post date:** [May 24, 2007, 4:27am UTC](https://www.ruby-forum.com/t/join-not-working/104935/4 "2007-05-24T04:27:47Z")
David M. wrote: > I’m not following. How does this help me considering I want to order by > rank which is not on the emails but it is on the codes table. Gabe is saying that the :joins option is ignored for finds on HABTM associations. So either write: Email.find :all, :order =\> ‘c.rank’, :conditions =\> “j.individual\_id = #{individual.id}”, :joins =\> \<\<-END as e inner join codes as c on e.type\_cd = c.type\_cd inner join emails\_individuals as j on e.id = j.email\_id END or if you can somehow set up an Email.belongs\_to :code association individual.emails.find :all, :order =\> ‘codes.rank’, :include =\> :code – We develop, watch us RoR, in numbers too big to ignore.