CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3associations
Hilmi Yamcı
Hilmi Yamcı

Reputation: 473

Ruby On Rails self join associations

I have two tables, and four models in my application. First model is company and it has companies table. Other models are employee,driver and supervisor. I've used single table inheritance in my application.

Company model:

class Company < ApplicationRecord

  has_many :employees

end

And table structure

ID   NAME
1    XXX company

And Employee, Driver and Supervisor models:

class Employee < ApplicationRecord

   belongs_to :company

end

class Chef < Employee


end

class Driver < Employee


end

class Supervisor < Employee


end

And Employees table structure:

ID NAME    COMPANY_ID TYPE
1  Jo      1          Supervisor
2  Jack    1          Driver
3  William 1          Driver
4  Avarell 1          Driver
5  Sam     1          Chef

What I need to do is that I want supervisors to access all drivers that belong to same company via a has_many assocations.

I have tried the following piece of code in supervisor class:

has_many :drivers, ->(supervisor) {where(company: supervisor.company)}

However, rails create the following sql and it is not what I'm expecting

SELECT `employees`.* FROM `employees` WHERE `employees`.`type` IN ('Driver') AND `employees`.`supervisor_id` = 4 AND `employees`.`type` IN ('Driver', 'Supervisor') AND `employees`.`company_id` = 1

I want rails to create such query while it's building the assocation.

SELECT `employees`.* FROM `employees` WHERE `employees`.`type` IN ('Driver') AND `employees`.`company_id` = 1

Any suggesstions,

Thanks.

Upvotes: 0

Views: 1005

Answers (1)

m. simon borg
m. simon borg

Reputation: 2575

You can do a has_many :through through a belongs_to association.

For example this will allow you to access a Supervisor's drivers and chefs from the same company. You will not be able to go backwards and find a Driver's supervisor.

class Company < ApplicationRecord
  has_many :employees
end

class Employee < ApplicationRecord
  belongs_to :company
end

class Chef < Employee
end

class Driver < Employee
end

class Supervisor < Employee
  has_many :drivers, through: :company, source: :employees, class_name: 'Driver'
  has_many :chefs, through: :company, source: :employees, class_name: 'Chef'
end

The SQL query it fires is

SELECT  "employees".* FROM "employees" INNER JOIN "companies" ON "employees"."company_id" = "companies"."id" WHERE "employees"."type" IN ('Driver') AND "companies"."id" = 1

Not exactly the query you envision, but it works.

Upvotes: 2

Related Questions