ActiveRecord find identical set in many_to_many models

Question

I have an anti-pattern in my Rails 3 code and I was wondering how to do this properly.

Let's say a customer orders french fries and a hamburger. I want to find out if such an order has been placed before. To keep it simple each item can only be ordered once per order (so no "two hamburgers please") and there are no duplicate orders either.

The models:

Order (attributes: id)
  has_many :items_orders
  has_many :items, :through => :items_orders

Item (attributes: id, name) 
  has_many :items_orders
  has_many :orders,:through => :items_orders

ItemsOrder (attributes: id, item_id, order_id)
  belongs_to :order
  belongs_to :item 
  validates_uniqueness_of :item_id, :scope => :order_id

The way I do it now is to fetch all orders that include at least one of the line items. I then iterate over them to find the matching order. Needless to say that doesn't scale well, nor does it look pretty.

order = [1, 2]

1 and 2 correspond to the Item ids of fries and hamburgers.

candidates = Order.find(
  :all, 
  :include => :items_orders, 
  :conditions =>  ["items_orders.item_id in (?)", order])

previous_order = nil

candidates.each do |candidate|
  if candidate.items.collect{|i| i.id} == order
    previous_order = candidate
    break
  end 
end

I'm using MySQL and Postgress so I'm also open for a standard SQL solution that ignores most of ActiveRecord.

Answer 1

Assuming you only want to find identical orders, I'd be tempted to use a hash to achieve this. I'm not able to test the code I'm about to write, so please check it before you rely on it!

Something like this: - Create a new attribute order_hash in your Order model using a migration. - Add a before_save hook that updates this attribute using e.g. an MD5 hash of the order lines. - Add a method for finding like orders which uses the hash to find other orders that match quickly.

The code would look a little like this:

class Order < ActiveRecord
  def before_save
    self.order_hash = calculate_hash
  end

  def find_similar
    Order.where(:order_hash => self.calculate_hash)
  end

  def calculate_hash
    d = Digest::MD5.new()
    self.items.order(:id).each do |i|
      d << i.id
    end
    d.hexdigest
  end
end

This code would allow you to create and order, and then calling order.find_similar should return a list of orders with the same items attached. You could apply exactly the same approach even if you had item quantities etc. The only constraint is that you have to always be looking for orders that are the same, not just vaguely alike.

Apologies if the code is riddled with bugs - hopefully you can make sense of it!