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 : myemail-zuv13RyHHZkAvxtiuMwx3w@public.gmane.org
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
"myemail-zuv13RyHHZkAvxtiuMwx3w@public.gmane.org" 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
--
Posted via http://www.ruby-forum.com/.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
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 Modernfossil 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 : myemail-zuv13RyHHZkAvxtiuMwx3w@public.gmane.org > 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 > "myemail-zuv13RyHHZkAvxtiuMwx3w@public.gmane.org" 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-- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
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. -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
David Modernfossil 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. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---