Rails - Mar 2009 - '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?
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Mar 10, 8:29 am, boblu
<bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> 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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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-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?

On 3月10日, 午後6:22, boblu <bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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.

On 3月10日, 午後11:23, boblu <bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
posthttp://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
posthttp://www.spacevatican.org/2008/6/22/the-difference-between-include-...,
> 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?
>
> On 3月10日, 午後6:22, boblu <bobl...-Re5JQEeQqe8@public.gmane.orgm>
wrote:
>
> > 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.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Mar 10, 4:33 pm, boblu
<bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---