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

**URL:** <https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473>\
**Category:** Rails\
**Created:** [March 10, 2009, 9:30am UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473 "2009-03-10T09:30:13Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [March 10, 2009, 9:30am UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473/1 "2009-03-10T09:30:13Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [March 10, 2009, 10:08am UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473/2 "2009-03-10T10:08:52Z")

</div>

On Mar 10, 8:29 am, boblu [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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](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

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [March 10, 2009, 10:23am UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473/3 "2009-03-10T10:23:32Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [March 10, 2009, 3:25pm UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473/4 "2009-03-10T15:25:14Z")

</div>

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](http://www.spacevatican.org/2008/4/29/include-and-conditions),  
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](http://www.spacevatican.org/2008/6/22/the-difference-between-include-and-joins),  
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?

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [March 10, 2009, 5:53pm UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473/5 "2009-03-10T17:53:11Z")

</div>

On Mar 10, 4:33 pm, boblu [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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

---

<div class="post-metadata">

**Author:** ![boblu](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/b19c9b/32.png) [@boblu](https://www.ruby-forum.com/u/boblu)\
**Post date:** [March 10, 2009, 5:34pm UTC](https://www.ruby-forum.com/t/unknow-column-error-when-using-include-and-associated-table-condition-in-find/162473/6 "2009-03-10T17:34:57Z")

</div>

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.