Chris
2009-Feb-14 00:43 UTC
association include and joins issues - ''Unknown Column'' error
Hi all,
I''d really appreciate any help / advice on this problem. When I test
this
def edit
@entity = Entity.find(params[:id],
:include => :key_factors)
@traits = Trait.find :all,
:include => :trait_values,
:joins => :trait_values,
:joins => "LEFT OUTER JOIN key_factors ON
key_factors.trait_value_id = trait_values.id AND
key_factors.entity_id="+@entity.id.to_s,
:order => ''traits.ordering,
traits.name''
@frequencies = Frequency.find :all,
:order => ''value''
end
I get the error "ActiveRecord::StatementInvalid: Mysql::Error: Unknown
column ''trait_values.id'' in ''on clause'':
SELECT `traits`.* FROM
`traits` LEFT OUTER JOIN key_factors ON key_factors.trait_value_id
trait_values.id AND key_factors.entity_id=953125641". I would have
thought that since I''m including trait_values then that table would be
in the SQL statement. However, what I see in the development log is:
[4;36 Entity Load (0.0ms) SELECT * FROM `entities` WHERE
(`entities`.`id` = 1)
[4;35 KeyFactor Load (0.0ms) SELECT `key_factors`.* FROM
`key_factors` WHERE (`key_factors`.entity_id = 1)
[4;36 Trait Load (0.0ms)Mysql::Error: Unknown column
''trait_values.id'' in ''on clause'': SELECT
`traits`.* FROM `traits` LEFT
OUTER JOIN key_factors ON key_factors.trait_value_id = trait_values.id
AND key_factors.entity_id=1 ORDER BY traits.ordering, traits.name
Which suggests that Rails is doing a separate call for each include...
and a bit of research on the API confirms that. However, in an earlier
version of rails this worked fine - the log for running the exact same
code shows (more or less - replaced a long field list with ''*''
to make
reading easier)
[4;36 Trait Load Including Associations (0.000000) SELECT * FROM
traits LEFT OUTER JOIN trait_values ON trait_values.trait_id traits.id LEFT
OUTER JOIN key_factors ON key_factors.trait_value_id trait_values.id AND
key_factors.entity_id=1 ORDER BY traits.ordering,
traits.name
My models are:
class Trait < ActiveRecord::Base
has_many :trait_values, :dependent => :destroy
end
class TraitValue < ActiveRecord::Base
belongs_to :trait
has_many :key_factors, :dependent => :destroy
has_many :trait_value_images, :foreign_key =>
''related_id'', :dependent => :destroy
def sorter
[(self.trait.ordering || "0"),(self.trait.name || "0"),
(self.ordering || "0"),(self.name || "")]
end
end
The API suggests that having conditions on a has_many association
might do the trick, but the condition would be to be able to be
dynamically specified ("...key_factors.entity_id="+@entity.id.to_s)
and I don''t see how that''s possible when defining the
condition on the
association in the model (not sure whether that''s because it actually
isn''t possible or just because I don''t know Rails well
enough).
Please help!
-Chris Warren
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Frederick Cheung
2009-Feb-14 10:26 UTC
Re: association include and joins issues - ''Unknown Column'' error
On Feb 14, 12:43 am, Chris <csw11...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi all, > > I''d really appreciate any help / advice on this problem. When I test > this > > def edit > @entity = Entity.find(params[:id], > :include => :key_factors) > @traits = Trait.find :all, > :include => :trait_values, > :joins => :trait_values, > :joins => "LEFT OUTER JOIN key_factors ON > key_factors.trait_value_id = trait_values.id AND > key_factors.entity_id="+...@entity.id.to_s, > :order => ''traits.ordering, traits.name'' > @frequencies = Frequency.find :all, > :order => ''value'' > end > > I get the error "ActiveRecord::StatementInvalid: Mysql::Error: Unknown > column ''trait_values.id'' in ''on clause'': SELECT `traits`.* FROM > `traits` LEFT OUTER JOIN key_factors ON key_factors.trait_value_id > trait_values.id AND key_factors.entity_id=953125641". I would have > thought that since I''m including trait_values then that table would be > in the SQL statement. However, what I see in the development log is: > > [4;36 Entity Load (0.0ms) SELECT * FROM `entities` WHERE > (`entities`.`id` = 1) > [4;35 KeyFactor Load (0.0ms) SELECT `key_factors`.* FROM > `key_factors` WHERE (`key_factors`.entity_id = 1) > [4;36 Trait Load (0.0ms)Mysql::Error: Unknown column > ''trait_values.id'' in ''on clause'': SELECT `traits`.* FROM `traits` LEFT > OUTER JOIN key_factors ON key_factors.trait_value_id = trait_values.id > AND key_factors.entity_id=1 ORDER BY traits.ordering, traits.name > > Which suggests that Rails is doing a separate call for each include... > and a bit of research on the API confirms that. However, in an earlier > version of rails this worked fine - the log for running the exact same > code shows (more or less - replaced a long field list with ''*'' to make > reading easier)Yup that is what happens. It should fall back to the old code in cases like this however I suspect that it doesn''t check the :joins clause for tables for that need to be included in the old way. I''m assuming you do actually want that include (and that it wasn''t just an easy way of triggering a join) since the answer is probably a lot easier if you don''t need the include. Rails will fall back to the old code whenever it sees something that looks like you''re using a table that isn''t the base table or provided through the joins clause. As I said, unfortunately I don''t think it checks the join clause in this way for references to tables. You should be able fix it by adding a dud condition that references the included table. I suspect it was just you poking around but doing :joins => :trait_values, :joins => "LEFT OUTER JOIN key_factors ON ... " doesn''t do anything since a hash can only have one value for a given key. YOu could also try :joins => "INNER JOIN trait_values ON ... LEFT OUTER JOIN key_factors ON ... " This would allow the query to run ok, however I suspect it would still process the include off the back of a second query. Fred> > [4;36 Trait Load Including Associations (0.000000) SELECT * FROM > traits LEFT OUTER JOIN trait_values ON trait_values.trait_id > traits.id LEFT OUTER JOIN key_factors ON key_factors.trait_value_id > trait_values.id AND key_factors.entity_id=1 ORDER BY traits.ordering, > traits.name > > My models are: > > class Trait < ActiveRecord::Base > has_many :trait_values, :dependent => :destroy > end > > class TraitValue < ActiveRecord::Base > belongs_to :trait > has_many :key_factors, :dependent => :destroy > has_many :trait_value_images, :foreign_key => > ''related_id'', :dependent => :destroy > > def sorter > [(self.trait.ordering || "0"),(self.trait.name || "0"), > (self.ordering || "0"),(self.name || "")] > end > > end > > The API suggests that having conditions on a has_many association > might do the trick, but the condition would be to be able to be > dynamically specified ("...key_factors.entity_id="+...@entity.id.to_s) > and I don''t see how that''s possible when defining the condition on the > association in the model (not sure whether that''s because it actually > isn''t possible or just because I don''t know Rails well enough). > > Please help! > > -Chris Warren--~--~---------~--~----~------------~-------~--~----~ 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@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Chris Warren
2009-Feb-15 00:20 UTC
Re: association include and joins issues - ''Unknown Column'' error
Excellent! Thanks for the clarification, Fred! I think adding the trait_values as an inner join will do the trick. -Chris On Sat, Feb 14, 2009 at 5:26 AM, Frederick Cheung <frederick.cheung-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > > > On Feb 14, 12:43 am, Chris <csw11...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> Hi all, >> >> I''d really appreciate any help / advice on this problem. When I test >> this >> >> def edit >> @entity = Entity.find(params[:id], >> :include => :key_factors) >> @traits = Trait.find :all, >> :include => :trait_values, >> :joins => :trait_values, >> :joins => "LEFT OUTER JOIN key_factors ON >> key_factors.trait_value_id = trait_values.id AND >> key_factors.entity_id="+...@entity.id.to_s, >> :order => ''traits.ordering, traits.name'' >> @frequencies = Frequency.find :all, >> :order => ''value'' >> end >> >> I get the error "ActiveRecord::StatementInvalid: Mysql::Error: Unknown >> column ''trait_values.id'' in ''on clause'': SELECT `traits`.* FROM >> `traits` LEFT OUTER JOIN key_factors ON key_factors.trait_value_id >> trait_values.id AND key_factors.entity_id=953125641". I would have >> thought that since I''m including trait_values then that table would be >> in the SQL statement. However, what I see in the development log is: >> >> [4;36 Entity Load (0.0ms) SELECT * FROM `entities` WHERE >> (`entities`.`id` = 1) >> [4;35 KeyFactor Load (0.0ms) SELECT `key_factors`.* FROM >> `key_factors` WHERE (`key_factors`.entity_id = 1) >> [4;36 Trait Load (0.0ms)Mysql::Error: Unknown column >> ''trait_values.id'' in ''on clause'': SELECT `traits`.* FROM `traits` LEFT >> OUTER JOIN key_factors ON key_factors.trait_value_id = trait_values.id >> AND key_factors.entity_id=1 ORDER BY traits.ordering, traits.name >> >> Which suggests that Rails is doing a separate call for each include... >> and a bit of research on the API confirms that. However, in an earlier >> version of rails this worked fine - the log for running the exact same >> code shows (more or less - replaced a long field list with ''*'' to make >> reading easier) > > Yup that is what happens. It should fall back to the old code in cases > like this however I suspect that it doesn''t check the :joins clause > for tables for that need to be included in the old way. > I''m assuming you do actually want that include (and that it wasn''t > just an easy way of triggering a join) since the answer is probably a > lot easier if you don''t need the include. > Rails will fall back to the old code whenever it sees something that > looks like you''re using a table that isn''t the base table or provided > through the joins clause. As I said, unfortunately I don''t think it > checks the join clause in this way for references to tables. > > You should be able fix it by adding a dud condition that references > the included table. I suspect it was just you poking around but doing > > :joins => :trait_values, > :joins => "LEFT OUTER JOIN key_factors ON ... " > > doesn''t do anything since a hash can only have one value for a given > key. > YOu could also try > > :joins => "INNER JOIN trait_values ON ... LEFT OUTER JOIN key_factors > ON ... " > > This would allow the query to run ok, however I suspect it would still > process the include off the back of a second query. > > Fred >> >> [4;36 Trait Load Including Associations (0.000000) SELECT * FROM >> traits LEFT OUTER JOIN trait_values ON trait_values.trait_id >> traits.id LEFT OUTER JOIN key_factors ON key_factors.trait_value_id >> trait_values.id AND key_factors.entity_id=1 ORDER BY traits.ordering, >> traits.name >> >> My models are: >> >> class Trait < ActiveRecord::Base >> has_many :trait_values, :dependent => :destroy >> end >> >> class TraitValue < ActiveRecord::Base >> belongs_to :trait >> has_many :key_factors, :dependent => :destroy >> has_many :trait_value_images, :foreign_key => >> ''related_id'', :dependent => :destroy >> >> def sorter >> [(self.trait.ordering || "0"),(self.trait.name || "0"), >> (self.ordering || "0"),(self.name || "")] >> end >> >> end >> >> The API suggests that having conditions on a has_many association >> might do the trick, but the condition would be to be able to be >> dynamically specified ("...key_factors.entity_id="+...@entity.id.to_s) >> and I don''t see how that''s possible when defining the condition on the >> association in the model (not sure whether that''s because it actually >> isn''t possible or just because I don''t know Rails well enough). >> >> Please help! >> >> -Chris Warren > > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---