Magento 2.3: Getting the salable quantity via SQL

Question

I'm trying to get all stock data of a product via SQL for debugging. The part that I'm not 100% on is the salable quantity.

Here is what I have:

SELECT t1.`quantity` + SUM(t2.`quantity`) salable_quantity
FROM `inventory_source_item` t1
JOIN `inventory_reservation` t2
ON t1.`sku` = t2.`sku` AND t1.`status` = 1
WHERE t1.`sku` = 'MY-PROD-SKU'

this works most of the time, and sometimes returns NULL.

I was hoping if any magento users had to write something similar, and have any improvements on this query. Thanks.

---

EDIT:

Improved version of my code:

SELECT COALESCE(t1.`quantity` + SUM(t2.`quantity`), 0) salable_quantity
FROM `inventory_source_item` t1
JOIN `inventory_reservation` t2
ON t1.`sku` = t2.`sku` AND t1.`status` = 1
WHERE t1.`sku` = 'MY-PROD-SKU'

Answer 1

I had a problem with your sql when loading products that didn't have any rows in the inventory_reservation table. Changed the SQL slightly.

SELECT t1.`quantity` + COALESCE(SUM(t2.`quantity`), 0) salable_quantity
FROM `inventory_source_item` t1
LEFT JOIN `inventory_reservation` t2
ON t1.`sku` = t2.`sku` AND t1.`status` = 1
WHERE t1.`sku` = 'MY-PROD-SKU'

Answer 2

The solution above does not take into account having multiple quantities at different source items. To improve the answer, one must also sum over all quantities of the inventory source items:

SELECT t1.`sku`, t1.`quantity` + COALESCE(t2.`quantity`, 0) as salable_quantity
FROM (SELECT `sku` as `sku`, SUM(IF(`status` = 1,`quantity`,0)) as quantity
      FROM `inventory_source_item`
      GROUP BY `sku`) AS t1
LEFT JOIN (SELECT `sku` as `sku`, SUM(`quantity`) as quantity
           FROM `inventory_reservation`
           GROUP BY `sku`) AS t2
ON t1.`sku` = t2.`sku`
where t1.`sku` = 'MY-PROD-SKU';