Using SQL INNER JOIN in ActiveRecord is really simple.

Let’s assume you have a relationship User has many Posts; User has a flag vip and Post have a flag approved. If you wanted to know the VIP Users with active Posts you would use something like:

User
  .joins(:posts)
  .where(posts: { approved: true })
  .where(vip: true)

Which is translated to the following SQL statement:

SELECT "users".* FROM "users"
INNER JOIN "posts" ON "posts"."user_id" = "users"."id"
WHERE "posts"."approved" = true AND "users"."vip" = true

All fine and dandy, but…

What if we wanted to know All VIP Users with approved Posts, including those with no approved Posts?

That is a SQL LEFT JOIN? Which in human terms means return everything on the left side and include the right even if there’s no right and in that case just nullify the right.

There’s not leftjoins or something similar in ActiveRecord (yet); so instead of that we have to use joins with a string and explicitly indicate the columns to use for the JOIN.

Our first try would look something like this:

User
  .joins('LEFT JOIN "posts" ON "posts"."user_id" = "users"."id"')
  .where(posts: { approved: true })
  .where(vip: true)

But that will not return those Users with zero Posts, recall the where statements apply after the JOINS happen, so in other words the query above is trying to apply a filter (after) to the nullified results.

What we need is something like this:

User
  .joins('LEFT JOIN "posts" ON "posts"."user_id" = "users"."id" AND "posts"."approved" = true')
  .where(vip: true)

Or if you are using dynamic parameters you could use something like:

join_conditions = '"posts"."user_id" = "users"."id" AND '"posts"."approved" = ?'
conditions = User.sanitize_sql_array([join_conditions, true])
User
  .joins("LEFT JOIN \"posts\" ON #{conditions}")
  .where(vip: true)

That will return the records you’re expecting to return.

Back to posts