Association include and joins issues - 'Unknown Column' error

Rails
1

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="[email protected]_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="[email protected]_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 W.

2

On Feb 14, 12:43 am, Chris [email protected] 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

3

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 C.