association include and joins issues - 'Unknown Column' error

rubyonrails-talk
1

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

2

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

3

Excellent! Thanks for the clarification, Fred! I think adding the trait_values as an inner join will do the trick.

  -Chris