:join not working


#1

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


#2

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


#3

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.


#4

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.