:join not working

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 : [email protected]
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
[email protected]” 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

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 : [email protected]
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
[email protected]” 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

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.

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 forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs