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 <[EMAIL PROTECTED]> 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 [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
