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


#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="removed_email_address@domain.invalid_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="removed_email_address@domain.invalid_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.


#3

On Mar 6, 1:48 pm, Kevin G. removed_email_address@domain.invalid
wrote:

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