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