Inventory SQL Query without UNION ?

Question

I'm designing an inventory system, it has users, products, buy_leads, orders (authorized buy_leads), inputs and outputs.

class Product < ActiveRecord::Base
  has_many :buy_leads
end

class BuyLead < ActiveRecord::Base
  belongs_to :product
  has_one :order
end

class Order < ActiveRecord::Base
  belongs_to :buy_lead
  belongs_to :user, :foreign_key => :authorized_by
  has_many :inputs
end

class Input < ActiveRecord::Base
  belongs_to :order
  has_many :outputs
end

class Output < ActiveRecord::Base
  # Associations
  belongs_to :input
  belongs_to :user
end

Inputs and Outputs have a quantity value. To get the products inventory, and specific product inventory I use UNION in two raw SQL queries, by making outputs quantities negative, and then group and sum them all together:

class InventoryController < ApplicationController

  def index
    @inventory = Input.find_by_sql products_inventory_sql
  end

  def show
    @inventory = Input.find_by_sql product_inventory_sql(params[:id])
  end

private

  def inputs_sql
    "SELECT b.*, p.*, i.order_id,
            i.id AS input_id,
            i.quantity AS quantity     
     FROM inputs i
          JOIN orders r ON r.id = i.order_id
          JOIN buy_leads b ON b.id = r.buy_lead_id
          JOIN products p ON p.id = b.product_id"
  end

  def outputs_sql
    "SELECT b.*, p.*, i.order_id,
            i.id AS input_id,
            (o.quantity * -1) AS quantity
     FROM outputs o
          JOIN inputs i ON i.id = o.input_id
          JOIN orders r ON r.id = i.order_id
          JOIN buy_leads b ON b.id = r.buy_lead_id
          JOIN products p ON p.id = b.product_id"
  end

  def products_inventory_sql
    "SELECT *, SUM(quantity) AS remaining_qty
     FROM (#{inputs_sql} UNION #{outputs_sql})
     GROUP BY product_id"
  end

  def product_inventory_sql(id)
    "SELECT *, SUM(quantity) AS remaining_qty
     FROM (#{inputs_sql} UNION #{outputs_sql})
     WHERE product_id = #{id}
     GROUP BY order_id, input_id"
  end

end

It works, but I would like to use named_scope's features to chain queries in ActiveRecord and be able to do things like:

Product.inputs.by_product(id)
Product.inventory.by_product(id)
...

Any ideas, or do I have to change the schema for a more convenient one ? Thanks!

Answer 1

I cannot test this without data but I think It should be something like this:

SELECT
        b.*, p.*, i.order_id, i.id AS input_id, i.quantity AS quantity, -o.quantity AS quantity,
        (i.quantity - COALESCE(o.quantity,0)) AS remaining_qty
FROM
        products p
        JOIN buy_leads b ON b.product_id = p.id
        JOIN orders r ON r.buy_lead_id = b.id
        JOIN inputs i ON i.order_id = r.id
        LEFT JOIN outputs o ON o.input_id = i.id

Answer 2

Victor's solution fails when there are multiple "output" records because the join will duplicate the products by BOTH the inputs and the outputs.

Instead, you should JOIN using a derived table instead of the actual table. Without data this is difficult to test and demonstrate but you should be trying something like this:

    "SELECT b.*, p.*, i.order_id,
        i.id AS input_id,
        i.quantity AS quantity,
ISNULL(z.negquantities,0) negquantities,
i.quantity + ISNULL(z.negquantities,0) sumquantities

 FROM inputs i
      JOIN orders r ON r.id = i.order_id
      JOIN buy_leads b ON b.id = r.buy_lead_id
      JOIN products p ON p.id = b.product_id
  JOIN 
    (SELECT SUM(-1 * o.quantity) NegQuantities, o.input_id FROM outputs o GROUP BY o.input_id) z
    ON z.input_id = i.id

You see that you are joining the aggregate sum of the output table, grouped by the input id, rather than the output table itself. This eliminates unintended row duplications in your joins. Of course, you can add more elements to the "ON" list or the "Where" clause of the derived table (which I called "z") This should get you most of the way there. Alternatively, post up a DB diagram so we understand your table relationships better.

Answer 3

There are too many possibilities to solve this, what exactly do you want from these reports?

the buy orders? the products? the inputs/outputs?

(I'm posting this as an answer because I cannot comment on your question, I'll update with answer if you could please enlighten me)

UPDATE

try this

#on input
named_scope :by_product, lambda {|id| {:joins => {:orders => {:buy_leads => :products}}, :conditions => ['products.id = ?', id]}}

and you can get the inputs that match that product id by calling

Input.by_product(25)

if that is what you were looking for I think you can manage to make the outputs by products too now :]