Active Record Associations - filter by association

Ruby on Rails
1

I could use some help on how to think about a thing.

I have a Rails app that has (for this question) two models like so…

class Room < ApplicationRecord
  belongs_to :building
end

class Building < ApplicationRecord
  has_many :rooms
end

Rooms come in many types, but I am primarily concerned with classrooms. So I have a scope set up on my Room model to limit for type classroom. 

scope :classrooms, -> { where(rmtyp_description: 'Classroom') }

There are a lot of Buildings in our system, a lot of which do not have classrooms, and so I don't really care about them...for now. I am looking for a way to returns just the Buildings that contain rooms that are of a type classroom. I know I can N+1 my way to a solution, but there has to be a better way. 

Any thoughts on how I can approach this?

Thanks,

Dave
1 Like
2

Hey @dschmura,

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
     FROM rooms
     WHERE rmtyp_description = 'Classroom'
    ) deduped_rooms
   USING (building_id)
SQL

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.

4 Likes
3

Thank you! This is exactly what I needed. I really appreciate you taking the time to show the non-AR approach as well. I will do some benchmarking along my path towards implementing this feature.

Thanks again!

Dave

1 Like