inventory

schema

/*
Products flow from one location to another. This is tracked with deliveries.

Delivery:
  amount
  sourceId
  targetId
  productId
*/
Delivery.belongsTo(Location, { as: 'source' })
Delivery.belongsTo(Location, { as: 'target' })
Delivery.belongsTo(Product, { as: 'product' })

// how to compute the stock/inventory of each product in each location?

// stock = inward_flow - outward_flow (for each product and location)
// inward_flow = sum(amount) where location is target
// outward_flow = sum(amount) where location is source

This is the SQL that does what I want.

SELECT
    "Locations".id, "Locations".name, "Products".id,
    SUM((CASE
        WHEN l.id = transactions."sourceId"
            THEN transactions.units * -1
            ELSE transactions.units
    END))
FROM
    "Locations"
INNER JOIN transactions ON "Locations".id = transactions."sourceId" OR "Locations".id = transactions."targetId"
INNER JOIN "Products" ON transactions."productId" = "Products".id
GROUP BY "Locations".id, "Products".id