Find all objects with no associated has_many objects

Question

In my online store, an order is ready to ship if it in the "authorized" state and doesn't already have any associated shipments. Right now I'm doing this:

class Order < ActiveRecord::Base
    has_many :shipments, :dependent => :destroy

    def self.ready_to_ship
     unshipped_orders = Array.new
     Order.all(:conditions => 'state = "authorized"', :include => :shipments).each do |o|
      unshipped_orders << o if o.shipments.empty?
     end
     unshipped_orders
    end
end

Is there a better way?

Answer 1

One option is to put a shipment_count on Order, where it will be automatically updated with the number of shipments you attach to it. Then you just

Order.all(:conditions => [:state => "authorized", :shipment_count => 0])

Alternatively, you can get your hands dirty with some SQL:

Order.find_by_sql("SELECT * FROM
  (SELECT orders.*, count(shipments) AS shipment_count FROM orders 
    LEFT JOIN shipments ON orders.id = shipments.order_id 
    WHERE orders.status = 'authorized' GROUP BY orders.id) 
  AS order WHERE shipment_count = 0")

Test that prior to using it, as SQL isn't exactly my bag, but I think it's close to right. I got it to work for similar arrangements of objects on my production DB, which is MySQL.

Note that if you don't have an index on orders.status I'd strongly advise it!

What the query does: the subquery grabs all the order counts for all orders which are in authorized status. The outer query filters that list down to only the ones which have shipment counts equal to zero.

There's probably another way you could do it, a little counterintuitively:

"SELECT DISTINCT orders.* FROM orders 
  LEFT JOIN shipments ON orders.id = shipments.order_id
  WHERE orders.status = 'authorized' AND shipments.id IS NULL"

Grab all orders which are authorized and don't have an entry in the shipments table ;)

Answer 2

You can also query on the association using the normal find syntax:

Order.find(:all, :include => "shipments", :conditions => ["orders.state = ? AND shipments.id IS NULL", "authorized"])