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
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.
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
Well to answer the question in the subject line, I wrote this a little
while back: The difference between :include and :joins - Space Vatican
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 ?
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.
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
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.
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.