Rails - Apr 2006 - two layers of has_many

Hi,

There are many companies. Each company has many departments. Each
department has many employees. The following find_by_sql method seems
awful. What is the best way to get all the employees of a company?

class Company < ActiveRecord::Base
  has_many :departments

  def employees
    Employee.find_by_sql("SELECT employees.*
            FROM companies, departments, employees
            WHERE companies.id=departments.company_id
            AND departments.id = employees.department_id"
            ORDER_BY employees.last_name)
  end
end

class Department < ActiveRecord::Base
  belongs_to :company
  has_many :employees
end

class Employee < ActiveRecord::Base
  belongs_to :department
end



I am using Rails 1.0 if that matters. Does it matter? Does the new
has_many :through work here?

Thanks,
Peter

Peter Michaux wrote:
> There are many companies. Each company has many departments. Each
> department has many employees. The following find_by_sql method seems
> awful. What is the best way to get all the employees of a company?
> 
> class Company < ActiveRecord::Base
>   has_many :departments
> 
Replace this:
>   def employees
>     Employee.find_by_sql("SELECT employees.*
>             FROM companies, departments, employees
>             WHERE companies.id=departments.company_id
>             AND departments.id = employees.department_id"
>             ORDER_BY employees.last_name)
>   end
> end
with
     has_many :employees, :through => :departments

and then you can do this:

    @company.employees

I think you can also tack an :order => :last_name clause on either has 
many to get exactly the order you want.


-- 

Ray

Ray Baxter wrote:
> Peter Michaux wrote:
> 
>> There are many companies. Each company has many departments. Each
>> department has many employees. The following find_by_sql method seems
>> awful. What is the best way to get all the employees of a company?
>>
>> class Company < ActiveRecord::Base
>>   has_many :departments
>>
> 
> Replace this:
> 
>>   def employees
>>     Employee.find_by_sql("SELECT employees.*
>>             FROM companies, departments, employees
>>             WHERE companies.id=departments.company_id
>>             AND departments.id = employees.department_id"
>>             ORDER_BY employees.last_name)
>>   end
>> end
> 
> with
>     has_many :employees, :through => :departments
> 
> and then you can do this:
> 
>    @company.employees
> 
> I think you can also tack an :order => :last_name clause on either has 
> many to get exactly the order you want.
Apropos of a later thread by you, this has_many through requires Rails 
1.1, so will not be able to use it until you upgrade.

I made the upgrade for just this reason, maybe you will too.

-- 

Ray