Alternative to a union with ActiveRecord

Question

I think I want to do a union in Rails, but according to this post rails union hack, how to pull two different queries together unions aren't natively supported in Rails. I'm wondering if there is a better way to approach this problem.

I have table of items, each item has many prices, but I only want to join one price to each item.

To determine the proper price for an item I have two additional foreign keys in the price model: category_id and discount_id. Each could independently declare a price for an item.

Ex. Item + Category = Price1 and Item + Discount = Price 2

If discount_id matches a passed id I want to exclude the price results FOR THAT ITEM ONLY that match Item + Category. Also I'm trying not to loose lazy loading.

Hopefully the problem is clear! If not I'll try to clarify more, thanks in advance.

I need some more clarification on this statement: "if discount_id matches a passed id". Also, what exactly is the association between a price and an item - which one holds the foreign key of the other? Can you give a concrete example with an item and some price models and what resulting calculation you would want in that case? — PinnyM, Jan 10 '13 at 22:46
A price has foreign keys for an item, a category, and a discount. The passing portion just means I'll have a discount_id parameter available in the controller. Lets say you have 3 prices p1 for item1 category1 p2 for item2 category1 and p3 for item2 discount4. If the discount_id parameter is 4 I want to get back p1 and p3 but not p2 because I already have a price for item2. It would be the union of the item_id + discounted_id prices with the item_id + category_id prices whose item_id didn't show up in the item_id + discounted_id list — Ben Fischer, Jan 11 '13 at 01:55

score 1 · Accepted Answer · answered Jan 11 '13 at 04:49

Your models would start off looking something like this:

class Price < ActiveRecord::Base
  belongs_to :item
  belongs_to :category
  belongs_to :discount

  scope :category, where("prices.category_id IS NOT NULL")
  scope :discount, where("prices.discount_id IS NOT NULL")
end

class Item < ActiveRecord::Base
  has_many :prices
end

class Category < ActiveRecord::Base
  has_many :prices
end

class Discount < ActiveRecord::Base
  has_many :prices
end

One way of doing this is to add a class method to Price that encapsulates this logic:

class Price < ActiveRecord::Base
  def self.used
    discount_items_sql = self.discount.select("prices.item_id").to_sql
    where("prices.discount_id IS NOT NULL OR prices.item_id NOT IN (#{discount_items_sql})")
  end
end

This is effectively the same as this query:

SELECT * FROM prices
WHERE prices.discount_id IS NOT NULL -- the discount_id is present on this record,
  OR prices.item_id NOT IN (         -- or no discount_id is present for this item
      SELECT item_id FROM prices WHERE discount_id IS NOT NULL)

You can add these helper methods on your Item model for simplicity:

class Item < ActiveRecord::Base
  def category_price
    prices.category.first
  end

  def discount_price
    prices.discount.first
  end

  def used_price
    prices.used.first
  end
end

Now you can easily get each individual 'type' of price for a single item (will be nil for prices that aren't available):

item.category_price
item.discount_price
item.used_price

Alternative to a union with ActiveRecord

1 Answers1