4 ways to filter has_many associations

You have a model that has_many associations, and you’d like to get a collection of these models with their associations that satisfies a list of conditions both on the model and the association records. You’ve tried a few things, but whatever you’ve tried only “sort of” works (meaning, not really) and leaves you feeling like you need a push in the right direction.

Filtering has_many associations can be one of those problems which makes you scratch your head and want to do some extra reading to brush up on your SQL and ActiveRecord knowledge.

For example, let’s say you have User and Project models in your system, and you want to write a query that retrieves a collection of users with projects that were created in a given date range.

Depending on what you want your query to return, here are 4 different ways to approach this problem:

  1. The simplest thing you can do is to combine the joins and where methods that ActiveRecord gives you, like so:

    User.joins(:projects).where(projects: { zipcode: 30332 })
    

    This technique is a good choice when you need to filter your records by one or more attributes. This will give you back a collection of User records which all have projects with a zipcode of 30332.

    One gotcha to keep in mind is that since you’re doing a INNER JOIN with the joins method, this can return duplicates. Read on to point 2 to see how you can get around this.

  2. More often than not, you might have scopes defined on your association models which you want to re-use. So when you want to use these scopes to do your filtering, get acquainted with ActiveRecord's merge function. According to the ActiveRecord documentation, merge returns an array representing the intersection of the ActiveRecord::Relation that you call merge on, and the ActiveRecord::Relation that you pass in.

    So, for example, if you have a scope in your Project model called opened_recently, which returns all projects which have been created within the last 10 days, you can do something like:

    User.joins(:projects).merge(Project.opened_recently)
    

    This will return to you a list of User objects which all satisfy the condition of having projects which were opened recently.

    Like I said before, one thing to keep in mind when you’re using joins for a has_many association is that since you are doing an INNER JOIN, it is possible that you will get back duplicate User (in our example) objects – basically one object for every ‘match’. It’s easy enough to get around this with a call to the uniq method.

    User.joins(:projects).merge(Project.opened_recently).uniq
    

    The uniq method works by changing your query from something like SELECT `users` from ... to something like SELECT DISTINCT `users` from...

  3. If you’re going to be needing to retrieve information from the records you filter, especially information stored in the associations of these records, another option to consider is eager loading and the includes method. As you probably know, the benefit of eager loading is that you get to avoid the N + 1 queries problem.

    It’s pretty straightforward to filter by attributes or scopes on the association:

    User.includes(:projects).where(projects: { zipcode: '30332' })
    

    This will give us all users who have projects in Atlanta, and also eager load their projects.

    If in your .where you want you use an SQL fragment, then you need to call the references method:

    User.includes(:projects).where('projects.deleted_at IS NOT NULL').references(:projects)
    

    You can also use the merge method in conjunction with includes and references to make use of any scopes you might have defined in your association models, like so:

    User.includes(:projects).merge(Project.opened_recently).references(:projects)
    

    You won’t need to use uniq when you use includes because ActiveRecord will take care of it for you automatically.

  4. Now, what should you do if you want your collection to only have those associations which meet your filter criteria? This situation arose for me when I had to write an API endpoint which let app users pass in parameters to filter associations. There are a couple of ways you can approach this. In my case, since I had to return an object that would eventually be converted to JSON, the simplest way was to manually construct a hash which contained the record and the filtered associations.

So I did something like this in my controller:

def show 
  user_attributes.merge(projects: filtered_projects) 
end


def user_attributes 
  # returns a hash containing attributes for the given user 
  # something like this: # { id: 1, first_name: 'Rick', last_name: 'Sanchez' } 
end

def filtered_projects 
  # here's where I used any Project related params to filter down my projects 
  # something like Project.opened_after(params[:project_date]).as_json 
  # I use as_json because I need this method to return a hash 
end

Note that the merge method used here is the one for Hash, and not the ActiveRecord method. You should be able to extend this to return multiple users as well.

Another way to do this is by defining a scoped has_many association. If you’re interested in seeing how this might be done, ping me in the comment section below and I’ll do a post on this topic specifically.

Hope this was helpful. As always, if you have a question about this topic that isn’t specifically addressed above, leave a comment below, and I’ll do my best to help out!

I love sharing tips, tricks and insights to make the craft and business of web development with Ruby and Rails faster, easier and more enjoyable.

Sign up to receive an update when I post a new article.

I also like sharing helpful and thought provoking articles by other smart people. Check out my most recent newsletter to see an example

11 thoughts on “4 ways to filter has_many associations

    1. Hi Andrew,

      I’ve written a bit about using scopes with has_many & belongs_to here. Let me know if that helps. Curious, is there a problem in particular you’re trying to solve?

      Sid

      1. That actually looks like it helps a lot! I just came across your blog for the first time today, thanks for the link to your other post 🙂

  1. Nice stuff, thank you. Annoyingly, not all methods work on has_one relationships, although its technically more or less the same. I cannot recall the details, because after I figured that joins will solve my problem in the most easy way, I had to move on. But I think it had to do with reflections (:user vs :userS).

  2. This is probably quite simple, but not for me: I’m trying to get from a Membership table the member_id integer and insert it into the User table. Both tables have a common field, :full_name
    The Membership table is being imported via csv, so it does not include a user_id. Is maybe the better idea to have a join table that has :full_name, :member_id and :user_id? I’l like to learn both techniques. Thanks

Leave a Reply

Your email address will not be published.