CODEWITHSUNDEEP
ruby-on-railspostgresql
Joe Sebin
Joe Sebin

Reputation: 450

join operation in ruby on rails for multiple tables

There are three tables in my postgresql database named "audits","locations","companies".

I want want to retrieve data by joining these tables.I have an executable sql query which gives my expected result.I want to convert this into ruby on rails.

select a.name as cname, b.name as lname, count(c.company_id) as count
from companies as a, locations as b, audits as c
where a.id=c.company_id and b.id =c.location_id
group by a.name,b.name;

Upvotes: 0

Views: 1119

Answers (1)

cristian
cristian

Reputation: 8744

I will try to recreate you models from your query ( I might get it wrong, so you should paste that along your query too):

class Company
  has_many :locations
  has_many :audits
end

class Location
  belongs_to :company
  has_many   :audits
end

class Audit
  belongs_to :company
  belongs_to :location
end

So to recreate your query do:

Company.joins(:locations, :audits)
  .group("companies.name", "locations.name")
  .count("audits.company_id")

The result will he a hash where the key is an array consisting of company_name and location_name and the value is the count like this: 

{[company_name, location_name] => count, ...}

This will result in the following query:

SELECT COUNT(audits.company_id) AS count_audits_company_id, companies.name AS companies_name, locations.name AS locations_name 
FROM "companies" INNER JOIN "locations" ON "locations"."company_id" = "companies"."id"
INNER JOIN "audits" ON "audits"."company_id" = "companies"."id" 
GROUP BY companies.name, locations.name

More info count

Upvotes: 1

Related Questions