order.rb

class Order < ActiveRecord::Base
  def self.fulfillable_for(product_id)
    select("orders.*, COUNT(up.product_id) AS unfulfillable").
    joins(:order_items).
    joins("LEFT JOIN order_items oi ON oi.order_id = order_items.order_id AND oi.product_id != order_items.product_id").
    joins("LEFT JOIN products up ON up.product_id = oi.product_id AND up.available_at > #{ActiveRecord::Base.sanitize(Date.today)}").
    where(order_items: { product_id: product_id} ).
    group("order_items.order_id").
    having("unfulfillable <= 0")
  end
  
  def self.arel_fulfillable_for(product_id)
    o = Order.arel_table
    i = OrderItem.arel_table
    oi = OrderItem.arel_table.alias("other_items")
    up = Product.arel_table.alias("unfulfillable_products")
    
    select([o["*"], up[:product_id].count.as("unfulfillable")]).
    joins(:order_items).
    joins(
      o.
      join(oi, Arel::Nodes::OuterJoin).on(
        oi[:order_id].eq(i[:order_id]).and(
        oi[:product_id].not_eq(i[:product_id])
      )).
      join(up, Arel::Nodes::OuterJoin).on(
        up[:product_id].eq(oi[:product_id]).and(
        up[:available_at].gt(Date.today))
      ).join_sql
    ).
    where(i[:product_id].eq(product_id)).
    group(i[:order_id]).
    having("unfulfillable <= 0")
  end
end