I had real weird problem here.
If I use "joins" in find, both development and production environment
give right answers.
But, when I use "include" in find, the development environment goes
all right. However, the find method fails in production enviroment.
Let me describe this in detail.
I have two tables.
###### table 1: companies ##########
id int
.....
###### table 2: sections ##########
id int
ref_company_id int
ref_meta_id int
....
A company will have one section, and a section may have sub-sections.
when ref_meta_id is 0, the section is the main section of a company
whose id is ref_company_id.
when ref_meta_id is not 0, the section is a sub-section of a company
whose id is ref_company_id.
And here are the two models
# class Company < ActiveRecord
# has_one :main-
section, :class=>"Section",
:foreign_key=>"ref_company_id",
:conditions=>"ref_meta_id=0"
# has_many :all-
sections, :class=>"Section",
:foreign_key=>"ref_company_id"
# end
# class Section < ActiveRecord
#
belongs_to :company, :class=>"Company",
:foreign_key=>"ref_company_id"
# end
All these things are good in both development and production
environment.
# Company.find(1).main-section
# Company.find(1).all-sections
# Section.find(1).company
Now comes to the find method used in controller.
First use joins, as I said before, the following methods went well in
both development and production enviroment.
# Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
sections], :conditions=>"companies.id<500")
# Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
sections], :conditions=>"sections.id<500")
Then use include,
# Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
sections], :conditions=>"companies.id<500")
this went well in both development and production enviroment.
However,
# Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
sections], :conditions=>"sections.id<500")
this went well in development environment, but in production
environment, I get this error.
*********************************************************************
Unknown column ''companies.ref_company_id'' in ''field
list'':
SELECT `companies`.`id` AS t0_r0,
`companies`.`ref_company_id` AS t0_r16,
`companies`.`ref_meta_id` AS t0_r17,
`sections`.`id` AS t1_r0,
`sections`.`ref_company_id` AS t1_r1,
`sections`.`ref_meta_id` AS t1_r2,
FROM `companies` LEFT OUTER JOIN `sections` ON
sections.ref_meta_id = companies.id
WHERE ( sections.id<500 )
**********************************************************************
And this is definetely a wrong SQL statement
Can anybody explain this?
And Can anybody please explain what is the difference between include
and join?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
OK. I figured out that "join" is actually doing an "inner join" which filters the rows that don''t have association. And "include" is actually doing an ''outter join'' which shows all the rows from tables. But, however, I still can not figure out why that strange SQL statement comes out. Can anyone please help me? On Nov 18, 1:52 pm, boblu <bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> I had real weird problem here. > > If I use "joins" in find, both development and production environment > give right answers. > But, when I use "include" in find, the development environment goes > all right. However, the find method fails in production enviroment. > > Let me describe this in detail. > > I have two tables. > > ###### table 1: companies ########## > id int > ..... > > ###### table 2: sections ########## > id int > ref_company_id int > ref_meta_id int > .... > > A company will have one section, and a section may have sub-sections. > when ref_meta_id is 0, the section is the main section of a company > whose id is ref_company_id. > when ref_meta_id is not 0, the section is a sub-section of a company > whose id is ref_company_id. > > And here are the two models > > # class Company < ActiveRecord > # has_one :main- > section, :class=>"Section", :foreign_key=>"ref_company_id", :conditions=>"ref_meta_id=0" > # has_many :all- > sections, :class=>"Section", :foreign_key=>"ref_company_id" > # end > > # class Section < ActiveRecord > # > belongs_to :company, :class=>"Company", :foreign_key=>"ref_company_id" > # end > > All these things are good in both development and production > environment. > # Company.find(1).main-section > # Company.find(1).all-sections > # Section.find(1).company > > Now comes to the find method used in controller. > First use joins, as I said before, the following methods went well in > both development and production enviroment. > # Company.find(:all, :select=>''companies.*'', :joins=>[:all- > sections], :conditions=>"companies.id<500") > # Company.find(:all, :select=>''companies.*'', :joins=>[:all- > sections], :conditions=>"sections.id<500") > > Then use include, > # Company.find(:all, :select=>''companies.*'', :joins=>[:all- > sections], :conditions=>"companies.id<500") > this went well in both development and production enviroment. > > However, > # Company.find(:all, :select=>''companies.*'', :joins=>[:all- > sections], :conditions=>"sections.id<500") > this went well in development environment, but in production > environment, I get this error. > > ********************************************************************* > Unknown column ''companies.ref_company_id'' in ''field list'': > SELECT `companies`.`id` AS t0_r0, > `companies`.`ref_company_id` AS t0_r16, > `companies`.`ref_meta_id` AS t0_r17, > `sections`.`id` AS t1_r0, > `sections`.`ref_company_id` AS t1_r1, > `sections`.`ref_meta_id` AS t1_r2, > FROM `companies` LEFT OUTER JOIN `sections` ON > sections.ref_meta_id = companies.id > WHERE ( sections.id<500 ) > ********************************************************************** > > And this is definetely a wrong SQL statement > > Can anybody explain this? > And Can anybody please explain what is the difference between include > and join?--~--~---------~--~----~------------~-------~--~----~ 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 finally get this right.
Here is the conclution.
In find method,
use ":join=>[:association_name]" will simply do a ''full
join'', which
drop all rows that do not match the association conditions.
use ":join=>[''join table_b on
table_a.id=table_b.xx'']", this is a
''full join'' too.
use ":join=>[''left(or right) join table_b on
table_a.id=table_b.xx'']",
this is the usual left or right join.
use ":include=>[:association_name]" will be supposed to do a
''left
outer join'', this will work most of the time.
but I don''t know why it sometimes generates wierd SQL statement like
this.
###### table 1: companies ##########
id int
.....
###### table 2: sections ##########
id int
ref_company_id int
ref_meta_id int
# class Company < ActiveRecord
#
has_one :main_section, :class=>"Section",
:foreign_key=>"ref_company_id",
:conditions=>"ref_meta_id=0"
#
has_many :all_sections, :class=>"Section",
:foreign_key=>"ref_company_id"
# end
# class Section < ActiveRecord
#
belongs_to :company, :class=>"Company",
:foreign_key=>"ref_company_id"
# end
Company.find(:all, :select=>''companies.*'', :include=>
[:all_sections], :conditions=>"sections.id<500")
*********************************************************************
Unknown column ''companies.ref_company_id'' in ''field
list'':
SELECT `companies`.`id` AS t0_r0,
`companies`.`ref_company_id` AS t0_r16,
`companies`.`ref_meta_id` AS t0_r17,
`sections`.`id` AS t1_r0,
`sections`.`ref_company_id` AS t1_r1,
`sections`.`ref_meta_id` AS t1_r2,
FROM `companies` LEFT OUTER JOIN `sections` ON
sections.ref_meta_id = companies.id
WHERE ( sections.id<500 )
**********************************************************************
On Nov 18, 2:57 pm, boblu
<bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> OK.
>
> I figured out that "join" is actually doing an "inner
join" which
> filters the rows that don''t have association.
> And "include" is actually doing an ''outter
join'' which shows all the
> rows from tables.
>
> But, however, I still can not figure out why that strange SQL
> statement comes out.
>
> Can anyone please help me?
>
> On Nov 18, 1:52 pm, boblu
<bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > I had real weird problem here.
>
> > If I use "joins" in find, both development and production
environment
> > give right answers.
> > But, when I use "include" in find, the development
environment goes
> > all right. However, the find method fails in production enviroment.
>
> > Let me describe this in detail.
>
> > I have two tables.
>
> > ###### table 1: companies ##########
> > id int
> > .....
>
> > ###### table 2: sections ##########
> > id int
> > ref_company_id int
> > ref_meta_id int
> > ....
>
> > A company will have one section, and a section may have sub-sections.
> > when ref_meta_id is 0, the section is the main section of a company
> > whose id is ref_company_id.
> > when ref_meta_id is not 0, the section is a sub-section of a company
> > whose id is ref_company_id.
>
> > And here are the two models
>
> > # class Company < ActiveRecord
> > # has_one :main-
> > section, :class=>"Section",
:foreign_key=>"ref_company_id",
:conditions=>"ref_meta_id=0"
> > # has_many :all-
> > sections, :class=>"Section",
:foreign_key=>"ref_company_id"
> > # end
>
> > # class Section < ActiveRecord
> > #
> > belongs_to :company, :class=>"Company",
:foreign_key=>"ref_company_id"
> > # end
>
> > All these things are good in both development and production
> > environment.
> > # Company.find(1).main-section
> > # Company.find(1).all-sections
> > # Section.find(1).company
>
> > Now comes to the find method used in controller.
> > First use joins, as I said before, the following methods went well in
> > both development and production enviroment.
> > # Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
> > sections], :conditions=>"companies.id<500")
> > # Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
> > sections], :conditions=>"sections.id<500")
>
> > Then use include,
> > # Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
> > sections], :conditions=>"companies.id<500")
> > this went well in both development and production enviroment.
>
> > However,
> > # Company.find(:all, :select=>''companies.*'',
:joins=>[:all-
> > sections], :conditions=>"sections.id<500")
> > this went well in development environment, but in production
> > environment, I get this error.
>
> > *********************************************************************
> > Unknown column ''companies.ref_company_id'' in
''field list'':
> > SELECT `companies`.`id` AS t0_r0,
> > `companies`.`ref_company_id` AS t0_r16,
> > `companies`.`ref_meta_id` AS t0_r17,
> > `sections`.`id` AS t1_r0,
> > `sections`.`ref_company_id` AS t1_r1,
> > `sections`.`ref_meta_id` AS t1_r2,
> > FROM `companies` LEFT OUTER JOIN `sections` ON
> > sections.ref_meta_id = companies.id
> > WHERE ( sections.id<500 )
> > **********************************************************************
>
> > And this is definetely a wrong SQL statement
>
> > Can anybody explain this?
> > And Can anybody please explain what is the difference between include
> > and join?
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
Well to answer the question in the subject line, I wrote this a little while back: http://www.spacevatican.org/2008/6/22/the-difference-between-include-and-joins A key thing to note is that include in 2.1 and include in 2.0.2 are different (but the 2.1 code will fall back to the 2.0.2 code if necessary). Does the companies table not have columns called ref_company_id ? 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
yes, the compannies table does not have a columns called ref_company_id. It is the table which is referreced by the sections table that has a ref_company_id as a foreign key. Can you explain why that error comes out? ''cause I cannot find any clue about it. And thank you for mentioning your blog post, and I now know why I feel my app is much faster using join than using include. Thank you. On Nov 18, 7:37 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Well to answer the question in the subject line, I wrote this a little > while back:http://www.spacevatican.org/2008/6/22/the-difference-between-include-... > A key thing to note is that include in 2.1 and include in 2.0.2 are > different (but the 2.1 code will fall back to the 2.0.2 code if > necessary). > Does the companies table not have columns called ref_company_id ? > > 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 -~----------~----~----~----~------~----~------~--~---
Harold to me As far as I know, the reason for :include is mainly for eager loading. If you know you will be querying the sections table for the companies you are finding, doing an :include will retrieve those sections in one query, ie, one trip to the DB. If you don''t pass in the :include option to the initial find, doing the_company.all-sections would go to the database to retrieve the associated records and then build the section object. I usually use :joins when I want to narrow down the search even further, for instance, to companies who have sections, persons who also have users, etc. For example: Company.find(:all, :include => :all-sections, :conditions => ''...'') Would fetch all companies meeting those conditions, along with the associated "all-section"s. Therefore it doesn''t make sense to force it to :select any specific columns, especially not columns on only one of the tables - it defeats the purpose of the :include. On the other hand: Company.find(:all, :joins => :all-sections, :conditions => ''..'', :select => ''company.*'') works fine, however, :select => ''company.*'' is redundant, and if you will need the returned companies'' sections, you will make a trip to the DB that may have been avoided by using :include. On Nov 18, 6:41 pm, boblu <bobl...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> yes, the compannies table does not have a columns called > ref_company_id. > It is the table which is referreced by the sections table that has a > ref_company_id as a foreign key. > Can you explain why that error comes out? ''cause I cannot find any > clue about it. > > And thank you for mentioning your blog post, and I now know why I feel > my app is much faster using join than using include. > Thank you. > > On Nov 18, 7:37 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: > > > Well to answer the question in the subject line, I wrote this a little > > while back:http://www.spacevatican.org/2008/6/22/the-difference-between-include-... > > A key thing to note is that include in 2.1 and include in 2.0.2 are > > different (but the 2.1 code will fall back to the 2.0.2 code if > > necessary). > > Does the companies table not have columns called ref_company_id ? > > > 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 18 Nov 2008, at 23:41, boblu wrote:> > yes, the compannies table does not have a columns called > ref_company_id. > It is the table which is referreced by the sections table that has a > ref_company_id as a foreign key. > Can you explain why that error comes out? ''cause I cannot find any > clue about it.It''s very weird - in particular it''s weird that it goes t0_r0, t0_r16: the number after the r is generated by an each_with_index loop - it should skip over the numbers 1-15. Weird stuff might happen if you had overwritten the column_names or columns methods on your ActiveRecord class but I would have expected that to cause problems elsewhere too. Fred> > > And thank you for mentioning your blog post, and I now know why I feel > my app is much faster using join than using include. > Thank you. > > On Nov 18, 7:37 pm, Frederick Cheung <frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > wrote: >> Well to answer the question in the subject line, I wrote this a >> little >> while back:http://www.spacevatican.org/2008/6/22/the-difference-between-include- >> ... >> A key thing to note is that include in 2.1 and include in 2.0.2 are >> different (but the 2.1 code will fall back to the 2.0.2 code if >> necessary). >> Does the companies table not have columns called ref_company_id ? >> >> 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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Sorry, that was my typo.
The correct error was:
###### table 1: companies ##########
id int
name string
###### table 2: sections ##########
id int
ref_company_id int
ref_meta_id int
sec_name string
class Company < ActiveRecord
has_one :main_section, :class=>"Section",
:foreign_key=>"ref_company_id",
:conditions=>"ref_meta_id=0"
has_many :all_sections, :class=>"Section",
:foreign_key=>"ref_company_id"
end
class Section < ActiveRecord
belongs_to :company, :class=>"Company",
:foreign_key=>"ref_company_id"
end
Company.find(:all, :select=>''companies.*'', :include=>
[:all_sections],
:conditions=>"sections.sec_name=''abc''")
*********************************************************************
Unknown column ''companies.ref_company_id'' in ''field
list'':
SELECT `companies`.`id` AS t0_r0,
`companies`.`name` AS t0_r1,
`companies`.`ref_company_id` AS t0_r2,
`companies`.`ref_meta_id` AS t0_r3,
`companies`.`sec_name` AS t0_r4,
`sections`.`id` AS t1_r0,
`sections`.`ref_company_id` AS t1_r1,
`sections`.`ref_meta_id` AS t1_r2,
`sections`.`sec_name` AS t1_r3,
FROM `companies` LEFT OUTER JOIN `sections` ON
sections.ref_meta_id = companies.id
WHERE ( sections.sec_name=''abc'' )
**********************************************************************
the werid part is
`companies`.`ref_company_id` AS t0_r2,
`companies`.`ref_meta_id` AS t0_r3,
`companies`.`sec_name` AS t0_r4,
For now, though I am using :join and :group to get what I want.
I still got no idea where these errors came from when using :include.
On Nov 19, 4:28 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 18 Nov 2008, at 23:41,bobluwrote:
>
>
>
> > yes, the compannies table does not have a columns called
> > ref_company_id.
> > It is the table which is referreced by the sections table that has a
> > ref_company_id as a foreign key.
> > Can you explain why that error comes out? ''cause I cannot
find any
> > clue about it.
>
> It''s very weird - in particular it''s weird that it goes
t0_r0, t0_r16:
> the number after the r is generated by an each_with_index loop - it
> should skip over the numbers 1-15.
> Weird stuff might happen if you had overwritten the column_names or
> columns methods on your ActiveRecord class but I would have expected
> that to cause problems elsewhere too.
>
> Fred
>
>
>
> > And thank you for mentioning your blog post, and I now know why I feel
> > my app is much faster using join than using include.
> > Thank you.
>
> > On Nov 18, 7:37 pm, Frederick Cheung
<frederick.che...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> > wrote:
> >> Well to answer the question in the subject line, I wrote this a
> >> little
> >> while
back:http://www.spacevatican.org/2008/6/22/the-difference-between-include-
> >> ...
> >> A key thing to note is that include in 2.1 and include in 2.0.2
are
> >> different (but the 2.1 code will fall back to the 2.0.2 code if
> >> necessary).
> >> Does the companies table not have columns called ref_company_id ?
>
> >> 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
-~----------~----~----~----~------~----~------~--~---