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