How do I fetch distinct joined records in Rails?

Question

I'm using Rails 3.0.10. A Building has_many Floors, and a Floor has_many Suites. I would like to fetch all the Buildings with at least one Suite. (Not every building has suites in it; some are still under construction, for example.)

Some caveats:

• I only want the unique records, so something like Building.joins(:floors, :suites) doesn't work.

• There are a lot of Buildings. I don't want to bring back a huge collection locally and then #uniq it.

• I'd prefer to avoid string references where possible. For example, I don't want to do something like Building.joins(:floors, :suites).select("distinct buildings.id").

• This can be done in a single SQL query -- something like select distinct buildings.id from buildings inner join floors on floors.building_id = buildings.id inner join suites on suites.floor_id;. So it's best if this approach also takes only one query.

What's the best way to go about this using ActiveRecord / ARel / anything else semantic that uses Rails core? I've come up with several different ways of doing this but I'm not sure what's the most canonical.

Answer 1

With 3 Sql-Queries:

Building.where(:id => Floor.where(:id => Suite.all.collect(&:floor_id).uniq).collect(&:building_id))