thr3ads.net - Rails - 2.2.2 issue - find :joins combining named assoc and SQL string [Feb 2009]

If this information is useful, please help other people find it:
Share via:

Chris

2009-Feb-06 02:45 UTC

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

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


--~--~---------~--~----~------------~-------~--~----~
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Kevin Gilpin

2009-Mar-06 13:48 UTC

head link

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

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.
-- 
Posted via http://www.ruby-forum.com/.

--~--~---------~--~----~------------~-------~--~----~
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

Frederick Cheung

2009-Mar-06 14:30 UTC

head link

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

On Mar 6, 1:48 pm, Kevin Gilpin
<rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org>
wrote:> 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> --
> Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

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

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

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