How to list all disabled products with sql?

Question

I need to list all disabled products by using a MySQL query.

I have too many products in my database to be using something like this (creating a collection, loading it and looping over it): http://www.srikanth.me/get-all-disabled-products-on-magento/

Answer 1

As magento product folow the EAV structure that

You need to write a Query between eav_attribute and catalog_product_entity_int table

Magento save product status on table catalog_product_entity_int table. Save it as 1 and 2.

• 1 for enable
• 2 for disable.

You need to get status attribute id using attribute code status, basically it is 96.

Query:

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
    SELECT attribute_id FROM `eav_attribute`
    WHERE `attribute_code` LIKE 'status'
) AND `catalog_product_entity_int`.value = 2

Answer 2

Magento Query

$productsCollection = Mage::getModel('catalog/product')->getCollection()
                    ->addAttributeToFilter('status', 2); // added enabled

Mysql Query

SELECT `e`.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status` 
FROM `catalog_product_entity` AS `e` 
INNER JOIN `catalog_product_entity_int` AS `at_status_default` 
 ON (`at_status_default`.`entity_id` = `e`.`entity_id`)
  AND (`at_status_default`.`attribute_id` = '96') 
  AND `at_status_default`.`store_id` = 0 
LEFT JOIN `catalog_product_entity_int` AS `at_status` 
 ON (`at_status`.`entity_id` = `e`.`entity_id`) 
  AND (`at_status`.`attribute_id` = '96') 
  AND (`at_status`.`store_id` = 1) 
WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '2')

Answer 3

@Amit Bera answer is the best but SQL request doesn't works if you have more than one attribute code named "status" (in my case I have total of 5 lines of "status") and MySQL will returns you: #1242 - Subquery returns more than 1 row error.

So I complete SQL query by adding the source_model as 'catalog/product_status' like this to works:

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
   SELECT attribute_id FROM `eav_attribute`
   WHERE `attribute_code` LIKE 'status'
   AND `source_model` LIKE 'catalog/product_status'
) AND `catalog_product_entity_int`.value = 2

Answer 4

Per Amits Post - I needed to find those "Disabled" Items (value of 2) . Here is a current mysql query with a few extra fields that I used to trouble shoot which products actually need to be "Enabled"

select
  `eav_attribute`.`attribute_id` AS `attribute_id`,
  `catalog_product_entity_int`.`entity_id` AS `entity_id`,
  `catalog_product_entity_int`.`value` AS `value`,
  `eav_attribute`.`attribute_code` AS `attribute_code`,
  `catalog_product_entity`.`sku` AS `sku`,
  `catalog_product_entity`.`created_at` AS `created_at`,
  `catalog_product_entity`.`updated_at` AS `updated_at`
from
  ((`eav_attribute`
  join `catalog_product_entity_int` on ((`eav_attribute`.`attribute_id` = `catalog_product_entity_int`.`attribute_id`)))
  join `catalog_product_entity` on ((`catalog_product_entity_int`.`entity_id` = `catalog_product_entity`.`entity_id`)))
where
  ((`eav_attribute`.`attribute_code` = 'status') and
  (`catalog_product_entity_int`.`value` = 2));

Answer 5

For M2:

$collection = $this->productCollectionFactory->create();
//$collection->addAttributeToSelect('name');
$collection->addAttributeToFilter('status', 1);

you can inject product collection factory:

private $productCollectionFactory;
public function __construct(
\Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productCollectionFactory
) {
$this->productCollectionFactory = $productCollectionFactory;
}

Generated query will be:

SELECT 
    `e`.*,
    IF(at_status.value_id > 0,
        at_status.value,
        at_status_default.value) AS `status`
FROM
    `catalog_product_entity` AS `e`
        INNER JOIN
    `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`)
        AND (`at_status_default`.`attribute_id` = '97')
        AND `at_status_default`.`store_id` = 0
        LEFT JOIN
    `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`)
        AND (`at_status`.`attribute_id` = '97')
        AND (`at_status`.`store_id` = 1)
WHERE
    (IF(at_status.value_id > 0,
        at_status.value,
        at_status_default.value) = '1')

By using ORM you dont need to bother about the status updated for you product against the STORE in table catalog_product_entity_int. It will generate query to check with this check already in it as you can see in above generated query its checking store value present in EAV table then use it else use default value.

Answer 6

Below example is per store and also if assigned to a website (store_id=1, website_id=1,status attribute_id=96)

SELECT 
`e`.sku, e.type_id,
IF(at_status.value_id > 0,
    at_status.value,
    at_status_default.value) AS `status`
FROM
`catalog_product_entity` AS `e`
    INNER JOIN `catalog_product_website` AS cpw ON (e.entity_id = cpw.product_id AND cpw.website_id = 1)
    INNER JOIN
`catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`row_id` = `e`.`row_id`)
    AND (`at_status_default`.`attribute_id` = '96')
    AND `at_status_default`.`store_id` = 0
    LEFT JOIN
`catalog_product_entity_int` AS `at_status` ON (`at_status`.`row_id` = `e`.`row_id`)
    AND (`at_status`.`attribute_id` = '96')
    AND (`at_status`.`store_id` = 1)
WHERE
(IF(at_status.value_id > 0,
    at_status.value,
    at_status_default.value) = '2')