Forum: Ruby on Rails association include and joins issues - 'Unknown Column' error

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.
D74cc7f3b588c1933e5417b99bb1ed59?d=identicon&s=25 Chris (Guest)
on 2009-02-14 01:44
(Received via mailing list)
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
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-02-14 11:27
(Received via mailing list)
On Feb 14, 12:43 am, Chris <csw11...@gmail.com> wrote:
>                          :joins => :trait_values,
> `traits`  LEFT OUTER JOIN key_factors ON key_factors.trait_value_id =
> 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
D74cc7f3b588c1933e5417b99bb1ed59?d=identicon&s=25 Chris Warren (Guest)
on 2009-02-15 01:22
(Received via mailing list)
Excellent! Thanks for the clarification, Fred! I think adding the
trait_values as an inner join will do the trick.

  -Chris


On Sat, Feb 14, 2009 at 5:26 AM, Frederick Cheung
This topic is locked and can not be replied to.