How can I find all the children of a bundle which are currently out-of-stock or have zero quantity

Question

Every now and then, a client creates a bundle product, and they include some simple products that aren't in stock. When this happens, they reach out and ask why their product isn't showing.

To expedite this process, it would be great to have a tool to troubleshoot common reasons that a bundle might not be working as intended.

How can I find all the products under a bundle that aren't in stock?

Answer 1

Currently, I'm using a database query to do this, but of course it would be ideal to use Magento collections instead.

Here's the query I use:

SET @bundle_sku='FG_REDBNDL'; 

SELECT sku 
FROM   catalog_product_entity 
WHERE  entity_id IN (SELECT product_id 
                     FROM   cataloginventory_stock_item 
                     WHERE  product_id IN(SELECT product_id 
                                          FROM   catalog_product_bundle_selection 
                                          WHERE  parent_product_id = (SELECT entity_id 
                                                                      FROM   catalog_product_entity
                                                                      WHERE  sku = @bundle_sku)) 
                            AND ( qty < 1 OR is_in_stock != 1 ));