Skip to content

Instantly share code, notes, and snippets.

@acuppy
Last active December 18, 2018 03:53
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save acuppy/84a6ea1c91a3f257f567 to your computer and use it in GitHub Desktop.
Using SQL JOINS to Retrieve ActiveRecord Objects Without Associated Records

Scenario: you're pulling all User records that do not have any associated Activities. In Rails you may find yourself doing something like this...

User.all.select { |user| user.activities.blank? }

Pretty simple to implement, but a performance hog: Rails will load a new ActiveRecord Object for each User, then call for all activities related to the user; simply to determine, which do and don't have any Activites: bad idea.

We can achieve the same result with a fraction of the overhead, using LEFT OUTER JOIN.

User.joins("LEFT OUTER JOIN activities on activities.user_id = users.id")
    .where(activities: { user_id: nil })

In the previous example, and all at the database layer, a LEFT OUTER JOIN will generate a complete set of User records from the Users table, then fill in the matching Activity records. When a User does not have an associated Activity, then its Activity record will show empty (null) values.

Using a where clause, we can call for only those User records for which the Activity is empty (null/nil).

Take away: Never forget the performant power of the database layer.

NOTE: The Rails default is a INNER join, which will only generate records from the Users table for which the Activity is NOT null.

TAGS: #rails #ruby #sql #PostgreSQL #howto

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment