Difference between include and joins in find?

[boblu]

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?

[boblu]

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?

[boblu]

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 )
**********************************************************************

[Frederick Cheung]

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

[boblu]

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...@gmail.com>
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-...

[boblu]

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.

[Frederick Cheung]

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

[boblu]

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...@gmail.com>
wrote: