Magento 2 - How to find all products with Salable qty as 0 via SQL query?

Question

Is there a simple SQL query I can run to find any ENABLED product in Magento that has a salable quantity which is 0?

We have thousands of products and in the back end product grid we can sort and range by Quantity, don't have the ability to filter or sort but via Salable Quantity column which is super frustrating, and there is many inaccuracies we are trying to resolve and its very time consuming to go through page after page in the grid.

Thanks

Answer 1

Try below query which will return the enabled products having 0 salable quantity.

SELECT t1.`sku`, (SUM(t1.`quantity`) + COALESCE(SUM(t2.`quantity`), 0)) AS `salable_quantity` FROM `inventory_source_item` t1 LEFT JOIN `inventory_reservation` t2 ON t1.`sku` = t2.`sku` AND t1.`status` = 1 GROUP BY t1.`sku` HAVING `salable_quantity` = 0 AND t1.`sku` NOT IN (SELECT `sku` FROM `catalog_product_entity` WHERE `row_id` IN (SELECT `row_id` FROM `catalog_product_entity_int` WHERE `attribute_id` = (SELECT `attribute_id`  FROM `eav_attribute` WHERE `attribute_code` = 'status' AND `entity_type_id` = 4) AND `catalog_product_entity_int`.value = 2));