LEFT OUTER JOIN in ActiveRecord

I always forget how to construct those queries in ActiveRecord, so here it goes.

Assuming we have the following structure:

class User < ActiveRecord::Base
  has_many :authentications

class Authentication < ActiveRecord::Base
  belongs_to :user

We can generate the LEFT OUTER JOIN SQL query in the following way (to see, for example, if we have dangling user references in authentications):

Authentication.joins('LEFT OUTER JOIN users ON authentications.user_id = users.id').where('users.id IS NULL').where('authentications.user_id IS NOT NULL')

Will generate the following SQL:

SELECT `authentications`.* FROM `authentications` LEFT OUTER JOIN users ON authentications.user_id = users.id WHERE (users.id IS NULL) AND (authentications.user_id IS NOT NULL)

i.e. it will select all authentications with incorrect (dangling) user_id references.

Published by

Paweł Gościcki

Ruby/Rails programmer.