Suppose you have the following Activerecord associations established:
And you want to find out which students have no class sessions. Often I’ve seen some interesting solutions like:
But this can get really bad if you ever have any reasonable amount of data to deal with. You can get what you want with a simple query:
When you use “includes” activerecord will use LEFT OUTER JOIN to include whatever associations you are asking for. In the result set, if associated records exist in the related tables, their values will will be included. If the associated record(s) don’t exist, the values for columns on the included association will be nil.
Which means if you check those associated columns for nil, you are essentially checking that the association is absent for that row. Hence,
Will, by checking for the absence of the class_sessions.id column, give you all the students without an associated class session.
What’s nice is that this applies to indirect associations via has_many through. Consider this arrangement where a student has class sessions through a join table called enrollments:
How would you check to find students without any class sessions here? Well, it’s the same:
Or if you wanted to be needlessly obtuse:
It’s probably better to check for nil on the actual associated record and not the join table, just so it is clear what you are checking for.
Ok let’s get crazy–what if you want to check on a really indirect association. So building off what we had before lets add two more classes:
Er..this example is getting a little convoluted but it could happen. Can you form a simple query to find students with no teachers? Sure!
The SQL that Activerecord generates is pretty gnarly but the Ruby/Rails is simple if not elegant.
Yep so that’s one way to look for unassociated primary records. If anyone has a better way or thinks I’ve said something stupid, let me know! I’m always ready to level up.