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:
-
The simplest thing you can do is to combine the
joins
andwhere
methods thatActiveRecord
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 thejoins
method, this can return duplicates. Read on to point 2 to see how you can get around this. -
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 theActiveRecord
documentation,merge
returns an array representing the intersection of theActiveRecord::Relation
that you callmerge
on, and theActiveRecord::Relation
that you pass in.So, for example, if you have a scope in your
Project
model calledopened_recently
, which returns allprojects
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 ahas_many
association is that since you are doing anINNER JOIN
, it is possible that you will get back duplicateUser
(in our example) objects – basically one object for every ‘match’. It’s easy enough to get around this with a call to theuniq
method.User.joins(:projects).merge(Project.opened_recently).uniq
The
uniq
method works by changing your query from something likeSELECT `users` from ...
to something likeSELECT DISTINCT `users` from...
-
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 thereferences
method:User.includes(:projects).where('projects.deleted_at IS NOT NULL').references(:projects)
You can also use the
merge
method in conjunction withincludes
andreferences
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 useincludes
becauseActiveRecord
will take care of it for you automatically.
- 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 would be interested in a quick post about scoped
has_many
relationships! 🙂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
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 🙂
Cheers!
(include resource about eager loading) – did you forget to replace this?
I did, and will do so. Thanks for reading!
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 thatjoins
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).Thanks Felix, that’s good to know.
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
Thank you very much for this information. Searched high and low to learn best method to join table with a where statement. Can I make a suggestion, I think that you should try to elevate (SEO) your presence so that this information can be more quickly found.
Thanks for the kind words Liz! If you found the article useful, I’d definitely appreciate a share on social media. Cheers!