1

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

2 Answers 2

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));
3
  • This worked! Thank you 🙏
    – jvb8
    Commented Mar 24, 2022 at 1:28
  • 1
    Your welcome! Can you please accept this as answer and up-vote it. Thanks! Commented Mar 24, 2022 at 5:26
  • Would love to up-vote but i'm new and don't have enough reputation to action. If you can up-vote my question, i'll be sure to return in kind once my reputation score allows me to!
    – jvb8
    Commented Mar 24, 2022 at 8:36
0

Updated Query:

SELECT t1.sku,
        SUM(t1.quantity) Qty, 
        COALESCE(SUM(t2.quantity), 0) Reserved, 
        (SUM(t1.quantity) + COALESCE(SUM(t2.quantity), 0)) AS Salable 
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 = 0 
AND t1.sku NOT IN 
   (
        SELECT sku FROM catalog_product_entity 
        WHERE entity_id IN (
            SELECT value_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 value = 2)
    ) 

Changing the HAVING Salable >= 0 will output the entire inventory

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.