This is a great question. I think the best way to handle this would be to query with a join so that you can do this in one query and leverage the speed of the database. Something like this:
possibly_duplicated_buildings = Building.joins(:rooms).merge(Room.classrooms)
Would get the correct set of buildings, but would also return a duplicated set of buildings if any buildings had multiple classrooms. You could de-dupe them with Ruby:
deduplicated_buildings = Building.joins(:rooms).merge(Room.classrooms).uniq(&:id)
That solution is easy to write, but if you had a lot of classrooms that shared buildings might eat up a lot of memory, as ActiveRecord would have to create a
Building object and all its associated memory footprint. Deduplicating using the database is probably the least-expensive solution:
deduplicated_buildings = Building.joins(<<-SQL)
LEFT JOIN (
SELECT DISTINCT ON (building_id) building_id, rmtyp_description
WHERE rmtyp_description = 'Classroom'
Here we joined against a temporary table,
deduped_rooms, that contains one row per
building_id that is associated with a classroom.
rmtyp_description doesn’t have to be
SELECTed, but I’ve included it for clarity.
The disadvantage of this approach is that now you have the knowledge of what’s a
classroom defined in two places (the AR scope on
Room and this SQL join), but this query isn’t an N+1 and will only create an in-memory
Building once for each of the desired result buildings.
I find that developers are often reluctant to use SQL views when they are using AR, but this kind of case, where you might query against this table often, might be a good scenario for a view. In any event, that query would be something you could make into its own scope.
Let me know if this helped you out.