Finding records without associations

Searching for entries having no associated objects in relational databases, requires to do a LEFT JOIN. An INNER JOIN would exclude exactly the wanted.

The Rails example

In a shop system, users can make orders.
The models:

rails g model User name:string
rails g model Order user:references

with the relations:

class Order < ApplicationRecord
  belongs_to :user

class User < ApplicationRecord
  has_many :orders

It is required to find all Users that do not have any order.

ARel Left Join

Up to Ruby on Rails 5, an appropriate solution could only be achieved with the flexibility of ARel.
However, not each Rails developer likes ARel. And it always was a cumbersome solution for a rather basic problem:

class User < ApplicationRecord
  has_many :orders

 def self.without_order
    orders            = reflect_on_association(:orders)
    order_arel        = orders.klass.arel_table
    user_primary_key  = arel_table[primary_key]
    order_foreign_key = order_arel[orders.foreign_key]
    orders_left_join  = arel_table.join(order_arel, Arel::Nodes::OuterJoin)
                                  .on(user_primary_key.eq order_foreign_key)
      .where(orders.table_name => { orders.klass.primary_key => nil} )

In this solution an Arel :: Nodes :: OuterJoin is generated from the two models ARel objects. The orders reflection provides the necessary association attributes for the Join condition. Finally, the ARel Node object generates the actual Join fragment, which is passed to joins.
The selection condition ensures that only those users are found that could not be joined with any orders. The call:


generates the expected SQL:

SELECT "users".*
  FROM "users"
  LEFT OUTER JOIN "orders" ON "users"."id" = "orders"."user_id"
  WHERE "orders"."id" IS NULL

ActiveRecord Left Join

Since ActiveRecord 5, there is finally the desired #left_joins:

User.left_joins(:orders).where(orders: { id: nil } )

It generates the same SQL.


A test:

require 'rails_helper'

RSpec.describe User, type: :model do
  subject { User.create! }
  before { User.create! orders: Array( }

  describe '.without_orders' do
    it 'returns all users without any order' do
      expect(User.without_order).to match_array(subject)

  describe '.left_joins' do
    it 'returns all users without any order' do
      expect(User.left_joins(:orders).where(orders: { id: nil } )).to match_array(subject)

proves, that both versions returns the same result:

Finished in 0.95742 seconds (files took 2.1 seconds to load)
2 examples, 0 failures