x

Retrieve the out-of-stock items in Magento 2, you can use the following SQL query

Queries for Product Stock and Status in Magento

Query to Get Out-of-Stock Items in Magento

SELECT
    cpe.sku,
    cpe.entity_id,
    cpe.type_id,
    cpe.attribute_set_id,
    cisi.qty,
    cisi.is_in_stock
FROM
    catalog_product_entity AS cpe
JOIN
    cataloginventory_stock_item AS cisi
    ON cpe.entity_id = cisi.product_id
WHERE
    cisi.is_in_stock = 0;

Query to Get Out-of-Stock Items That Are Enabled

SELECT
    cpe.sku,
    cpe.entity_id,
    cpe.type_id,
    cpe.attribute_set_id,
    cisi.qty,
    cisi.is_in_stock,
    cpei.value AS status
FROM
    catalog_product_entity AS cpe
JOIN
    cataloginventory_stock_item AS cisi
    ON cpe.entity_id = cisi.product_id
JOIN
    catalog_product_entity_int AS cpei
    ON cpe.entity_id = cpei.entity_id
JOIN
    eav_attribute AS ea
    ON cpei.attribute_id = ea.attribute_id
WHERE
    cisi.is_in_stock = 0
    AND ea.attribute_code = 'status'
    AND cpei.value = 1; -- 1 means 'Enabled'

Query to Get Products That Are Not Allowed Backorder

SELECT
    cpe.sku,
    cpe.entity_id,
    cpe.type_id,
    cpe.attribute_set_id,
    cisi.qty,
    cisi.is_in_stock,
    cisi.backorders
FROM
    catalog_product_entity AS cpe
JOIN
    cataloginventory_stock_item AS cisi
    ON cpe.entity_id = cisi.product_id
WHERE
    cisi.backorders = 0; -- Backorders not allowed

Query to Get Enabled Products That Are Not Allowed Backorder

SELECT
    cpe.sku,
    cpe.entity_id,
    cpe.type_id,
    cpe.attribute_set_id,
    cisi.qty,
    cisi.is_in_stock,
    cisi.backorders
FROM
    catalog_product_entity AS cpe
JOIN
    cataloginventory_stock_item AS cisi
    ON cpe.entity_id = cisi.product_id
JOIN
    catalog_product_entity_int AS cpei
    ON cpe.entity_id = cpei.entity_id
JOIN
    eav_attribute AS ea
    ON cpei.attribute_id = ea.attribute_id
WHERE
    cisi.backorders = 0 -- Backorders not allowed
    AND ea.attribute_code = 'status' -- Check for the status attribute
    AND cpei.value = 1; -- Status = Enabled

Tags

↑