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

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?

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

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.

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?

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.

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