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
end
class User < ApplicationRecord
has_many :orders
end
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)
.join_sources
joins(orders_left_join)
.where(orders.table_name => { orders.klass.primary_key => nil} )
end
end
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:
User.without_order
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.
Tested
A test:
require 'rails_helper'
RSpec.describe User, type: :model do
subject { User.create! }
before { User.create! orders: Array(Order.new) }
describe '.without_orders' do
it 'returns all users without any order' do
expect(User.without_order).to match_array(subject)
end
end
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)
end
end
end
proves, that both versions returns the same result:
Finished in 0.95742 seconds (files took 2.1 seconds to load)
2 examples, 0 failures