Forum: Ruby on Rails 'unknow column error' when using include and associated table condition in find

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.
boblu (Guest)
on 2009-03-10 10:30
(Received via mailing list)
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?
Frederick C. (Guest)
on 2009-03-10 11:08
(Received via mailing list)
On Mar 10, 8:29 am, boblu <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/br...
- 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
boblu (Guest)
on 2009-03-10 11:23
(Received via mailing list)
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.
boblu (Guest)
on 2009-03-10 16:25
(Received via mailing list)
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
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
http://www.spacevatican.org/2008/6/22/the-differen...,
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?
boblu (Guest)
on 2009-03-10 18:34
(Received via mailing list)
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.
Frederick C. (Guest)
on 2009-03-10 18:53
(Received via mailing list)
On Mar 10, 4:33 pm, boblu <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
This topic is locked and can not be replied to.