2.2.2 issue - find :joins combining named assoc and SQL string

rubyonrails-talk
1

Hi all,

I have a piece of code that worked perfectly in an older version of rails, but dies in 2.2.2 and I'd love any help anyone could give me getting it working again.

  @entity = Entity.find(params[:id])   @traits = Trait.find :all,    :include => :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'

The second find, on Trait, is giving me the problem-

"...Unknown column 'trait_values.id' in 'on clause'..."

The 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 end

class KeyFactor < ActiveRecord::Base   belongs_to :trait_value   belongs_to :entity end

class Entity < ActiveRecord::Base   has_many :key_factors, :dependent => :destroy end

In the older version of rails I think the underlying SQL was generated as a single query (SELECT blah FROM traits INNER JOIN trait_values ON ... LEFT OUTER JOIN key_factors ON...). However, in 2.2.2 the included association seems (based on checking the log file) to be loaded in a separate query, which means the system chokes on

   key_factors.trait_value_id = trait_values.id

because there is no trait_values table mentioned in the SQL of the primary query. The API docs suggest that the :joins parameter can take a combination of associations and SQL strings:

"# :joins - Either an SQL fragment for additional joins like "LEFT JOIN comments ON comments.post_id = id" (rarely needed), named associations in the same form used for the :include option, which will perform an INNER JOIN on the associated table(s), or an array containing a mixture of both strings and named associations. If the value is a string, then the records will be returned read-only since they will have attributes that do not correspond to the table‘s columns. Pass :readonly => false to override."

I tried

  @traits = Trait.find :all,    :include => [:trait_values],    :joins => [:trait_values,"LEFT OUTER JOIN key_factors ON key_factors.trait_value_id = trait_values.id AND..."],    :order => 'traits.ordering, traits.name'

but the system then gives me the error message

"Association named 'LEFT OUTER JOIN key_factors ON key_factors.trait_value_id = trait_values.id AND key_factors.entity_id=1' was not found; perhaps you misspelled it?"

So, it's treating the SQL string as a named association, even though the API docs say that associations and strings can be mixed. I also tried this-

    @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'

which gives me the same original error message: "...Unknown column 'trait_values.id' in 'on clause'..."

If nothing else works I can explicitly state the SQL for the whole join mess (or the whole query, for that matter), but I'd really like to take advantage of the associations I've already defined (since, among other things, that's part of the point of doing this project in rails). Any pointers or ideas or explanations of what I'm failing to understand?

Thanks all

  -Chris

2

I have the same problem. A query which was previously:

macro_step = Step.find :first, :include => [ :parameters ], :conditions => [ "name = 'delegate_id' AND int_v = ?", macro.id ]

failed with the message

ActiveRecord::StatementInvalid: Mysql::Error: Unknown column 'name' in 'where clause': SELECT * FROM `step` WHERE (name = 'delegate_id' AND int_v = 92) LIMIT 1

Because the delegate_id column is actually on the parameters table.

Rewriting the query as:

macro_step = Step.find :first, :joins => [ :parameters ], :conditions => [ "name = 'delegate_id' AND int_v = ?", macro.id ]

worked for me in this case because the join did not need to be an OUTER JOIN. But this is clearly a Rails bug.

If you disambiguate your table names (ie parameters.int_v) then :include will fall back to the old code. If you only need a join rather than include then you should really just stick with the join.

Fred