I have a 3 simple models, let's call them Sector, Department, Office. Sector has many departments and departments has many offices.
Now I want to get all the sectors with at least a department that has at least an office.
I tried a couple of different ways and they "more or less" work. I mean I get what I want if I call Sector.with_offices however If I chain some pretty common methods, such as .size (that adds a count to the original query), I get an unexpected results. Here what I have tried:
scope :with_offices, joins(:departments => :offices).group('sectors.id')
scope :with_offices, joins(:departments => :offices).select("DISTINCT sectors.*")
I also tried using uniq:
scope :with_offices, joins(:departments => :offices).uniq
But, it has the same issue.
Sector.with_offices.size # => 5 (WRONG VALUE)
s = Sector.with_offices # => [#<Sector ... >]
s.size # => 3 (RIGHT VALUE)
If I chain size I get the wrong number.
What's a clean way to get sectors with offices and keep size working as expected?
UPDATE 1 - THE SQL QUERIES Here my queries, I forgot to mention that both associations have a conditions clause that restrict on the state (as shown below).
irb(main):010:0> Sector.with_offices.size
(0.6ms) SELECT DISTINCT COUNT(*) FROM "sectors" INNER JOIN "departments" ON "departments"."sector_id" = "sectors"."id" AND departments.state IN ('active', 'deactivated') INNER JOIN "offices" ON "offices"."department_id" = "departments"."id" AND offices.state IN ('active', 'deactivated') WHERE "sectors"."state" IN ('active', 'deactivated')
=> 5
irb(main):011:0> s = Sector.with_offices
Sector Load (0.6ms) SELECT DISTINCT "sectors".* FROM "sectors" INNER JOIN "departments" ON "departments"."sector_id" = "sectors"."id" AND departments.state IN ('active', 'deactivated') INNER JOIN "offices" ON "offices"."department_id" = "departments"."id" AND offices.state IN ('active', 'deactivated') WHERE "sectors"."state" IN ('active', 'deactivated')
UPDATE 2 - THE ASSOCIATIONS
Class Sector < ActiveRecord::Base
has_many :departments , conditions: ["departments.state IN ('active', 'deactivated')"]
end
Class Department < ActiveRecord::Base
has_many :offices , conditions: ["offices.state IN ('active', 'deactivated')"]
end
I tried it and it's a bug in 3.2.12, which has apparently been fixed in 3.2.13. If you upgrade to 3.2.13 the correct SQL will be generated.