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 <[email protected]> wrote: > > > > On Feb 14, 12:43 am, Chris <[email protected]> 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="[email protected]_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="[email protected]_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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
