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

Product.findAll({
  include: [
    {
      model: Delivery,
      attributes: [
        ['SUM(product.delivery.source.id)', 'in'],
        ['SUM(product.delivery.target.id)', 'out']
      ],
      include: [
        {model: Location, as: 'source'},
        {model: Location, as: 'target'}
      ]
    }
  ],
  group: [
    'product.delivery.id',
    'product.id'
  ]
});

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