Forum: Ruby on Rails :join not working

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.
David M. (Guest)
on 2007-05-24 04:11
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
Gabe D. (Guest)
on 2007-05-24 04:22
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
David M. (Guest)
on 2007-05-24 04:42
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.
Mark Reginald J. (Guest)
on 2007-05-24 06:27
(Received via mailing list)
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.
This topic is locked and can not be replied to.