'unknow column error' when using include and associated table condition in find

Rails
1

Here is my weird problem.
Two models like following:

Lexeme
id:int
name:string

Structure
id:int
ref_id:string
meta_id:int

Lexeme can have many structure records through foreign key ‘ref_id’ in
the structures table.
And these structure records belonging to one lexeme differs between
each other using meta_id.
And if Lexeme has structures, then there must be a top_struct whose
structure.id is 0.
This is the association I specified.

class Lexeme

has_one :top_struct, :class=>‘Structure’, :foreign_key=>‘ref_id’,
:conditions=>‘structures.meta_id=0’
has_many: all_structs, :class=>‘Structure’, :foreign_key=>‘ref_id’
end

class Structure
belongs_to :lexeme, :class=>‘Lexeme’, :foreign_key=>‘ref_id’
end

After these definition, say I want to find ‘those lexemes that have
structures when structures.id <10’.
I tried the following two find

Lexeme.find
(:all, :include=>:top_struct, :conditions=>‘structures.id<10’)
Lexeme.find
(:all, :include=>:all_structs, :conditions=>‘structures.id<10’)

Then finds gives out same error
For the first one
ActiveRecord::StatementInvalid: Mysql::Error: Unknown column
‘lexemes.ref_id’ in ‘field list’:
SELECT lexemes.id AS t0_r0,
lexemes.name AS t0_r1,
lexemes.ref_id AS t0_r2,
lexemes.meta_id AS t0_r3,
structures.id AS t1_r0,
structures.ref_id AS t1_r1,
structures.meta_id AS t1_r2,
FROM lexemes LEFT OUTER JOIN structures ON structures.ref_id =
lexemes.id and structures.meta_id=0
WHERE (structures.id<10)

For the second one
ActiveRecord::StatementInvalid: Mysql::Error: Unknown column
‘lexemes.ref_id’ in ‘field list’:
SELECT lexemes.id AS t0_r0,
lexemes.name AS t0_r1,
lexemes.ref_id AS t0_r2,
lexemes.meta_id AS t0_r3,
structures.id AS t1_r0,
structures.ref_id AS t1_r1,
structures.meta_id AS t1_r2,
FROM lexemes LEFT OUTER JOIN structures ON structures.ref_id =
lexemes.id
WHERE (structures.id<10)

Apparently, my ‘lexemes’ table dose not have ‘ref_id’ and ‘meta_id’
field.
And I’m using rails 2.2.2

But when I change the ‘conditions’ field to conditions on lexemes
table, everything goes fine.
Lexeme.find(:all, :include=>:top_struct, :conditions=>‘lexemes.id<10’)
Lexeme.find
(:all, :include=>:all_structs, :conditions=>‘lexemes.id<10’)

Can anyboby please explain to me why this is happening?

2

On Mar 10, 8:29 am, boblu [email protected] wrote:

Can anyboby please explain to me why this is happening?
Seems like you had the same problem 5 months ago:
http://groups.google.com/group/rubyonrails-talk/browse_thread/thread/5d54c2717944fe39/ebd8b583787ae63a?lnk=gst&q=include+column#ebd8b583787ae63a

  • did you get to the bottom of that? the reason there’s a difference
    when you don’t have a condition on the structures table is that in
    that case AR does the include in a completely different way.

Fred

3

Fred, thanks for the quick reply.
I did ask the same problem 5 month ago.
I managed to use join instead of include like this

Lexeme.find(:all, :conditions=>‘structures.id<10’, :joins=>‘left outer
join structures on structures.ref_id=lexemes.id and
structures.meta_id=0’)

Since usually I need to specify conditions that have both lexemes
table and structures table’s fields,
I used SQL instead of regular association in the above joins,
because the regular association joins does a inner join which will
give me nothing when I only want to find those lexemes without any
structures.

And now (5 month later), the reason I ask this question again are

  1. nobody gave me a reason why include goes wrong
  2. I really want a collection with eager loading instead of piles of
    rows which joins gives.

Sorry for my foolness, but I don’t quite understand what you said here

the reason there’s a difference
when you don’t have a condition on the structures table is that in
that case AR does the include in a completely different way.

Can you please explain this to me in details?
Thank you.

4

I forgot to mention that these error only come out in production
environment.
Everything goes well in development environment.

I don’t understand why the action of find is different in production
and development environment.

I have read this post
Mixing :include and :conditions - Space Vatican,
and I know there are old and new ways of eager loading in rails.
And in my case, the old way is exactly what I want to do.

the last example in this post
The difference between :include and :joins - Space Vatican,
did the same thing I post here, and it seems everything went ok, just
like mine in development environment.

I’m wondering are there anyone try running the same find in production
environment?
And does it output the same error?

5

On Mar 10, 4:33 pm, boblu [email protected] wrote:

So, after re-examine, I found that I have changed Lexeme.column_names
in another models in validation process.
Everything goes well in development environment because in development
environment ‘config.cache_classes’ is set to false.
so every request will reload code, and Lexeme.column_names get the
right value every time.
But in production environment, ‘config.cache_classes’ is set to true,
my app coda get loaded only once,
so I did not notice that Lexeme.column_names has been changed in other
process.

Yup that would do it. All i meant by the last comment was that AR has
2 eager loading strategies, as explained by that blog post of mine you
found. Glad you got to the bottom of it!

Fred

6

So sorry for the big argue.

After digging the following
find --> find_every --> find_with_associations
–> select_all_rows -->
construct_finder_sql_with_included_associations
–> column_aliases,

I suddenly found that in production environment,
Lexeme.column_names 's result and
Lexeme.columns.map{ |column| column.name } 's are having big
difference.

This is definitely wrong, and I realize this is definitely a bug in my
app, not in rails.

So, after re-examine, I found that I have changed Lexeme.column_names
in another models in validation process.
Everything goes well in development environment because in development
environment ‘config.cache_classes’ is set to false.
so every request will reload code, and Lexeme.column_names get the
right value every time.
But in production environment, ‘config.cache_classes’ is set to true,
my app coda get loaded only once,
so I did not notice that Lexeme.column_names has been changed in other
process.

Anyway, I had a better idea of find, include, joins and the whole find
action in finding this bug.
Fred, thanks for your advices.