Find all records with no associated records in db

Question

I have two models

class Checkout
 has_one :order
end

class Order 
  belongs_to :checkout
end

I need to find all checkouts that has no order yet. How can I do it with rails?

`Checkout.includes(:order).where(orders: { id: nil })` can list them. — ray, Jan 24 '19 at 12:47

score 2 · Accepted Answer · answered Jan 24 '19 at 12:42

2

Use left outer join that would give you all checkouts joined with orders. Then select rows without orders (order.id == nil)

Chceckout.left_outer_join(:orders).where('orders.id' => nil)

answered Jan 24 '19 at 12:42

mrzasa

22,895
11
56
94

score 0 · Answer 2 · answered Jan 24 '19 at 13:17

0

The trick here is that includes() expects the name of the association but the where expects the name of the table. For a has_one the association will generally be expressed in the singular, so that changes, but the where() part stays as it is.So statement would be:

  Checkout.includes(:order).where( :orders=> { :orders_id=> nil } )

answered Jan 24 '19 at 13:17

Giridharan

568
4
14

`where( :orders=> { :orders_id=> nil } )` will query like `.where('orders.order_id IS NULL)` in SQL which leads to wrong answer. – ray Jan 25 '19 at 13:59

Find all records with no associated records in db

2 Answers2