mysql - 如何在M2中通过sql查询所有库存产品的类别ID？

Question

正如标题所述，我希望在 Magento 2 中按类别 ID 查询所有库存产品。如果可能的话，如果 simple 有库存，则仅获取父级(配置)ID，不确定我是否有意义？

SELECT p.entity_id as product_id, p.sku, c.is_in_stock 
FROM catalog_product_entity as p
INNER JOIN cataloginventory_stock_item as c 
ON p.entity_id = c.product_id

Answer 1

要获取某个类别 ID 的所有库存产品:

SELECT product.entity_id as product_id, product.sku, stock_item.is_in_stock FROM catalog_product_entity as product
    INNER JOIN cataloginventory_stock_item as stock_item ON product.entity_id = stock_item.product_id
    INNER JOIN catalog_category_product as cat_prod on product.entity_id = cat_prod.product_id
    WHERE cat_prod.category_id=XXX and stock_item.is_in_stock=1;

如果您只需要可配置项，只需添加如下条件:and Product.type_id='configurable'