Forum: Ruby on Rails 2.2.2 issue - find :joins combining named assoc and SQL string

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Chris (Guest)
on 2009-02-06 04:46
(Received via mailing list)
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
Kevin G. (Guest)
on 2009-03-06 15:48
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.
Frederick C. (Guest)
on 2009-03-06 16:31
(Received via mailing list)
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
This topic is locked and can not be replied to.