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 <[EMAIL PROTECTED]> 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 <[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
-~----------~----~----~----~------~----~------~--~---
