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/


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.


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

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')

@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

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"

  `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`
  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`)))
  ((`eav_attribute`.`attribute_code` = 'status') and
  (`catalog_product_entity_int`.`value` = 2));
  • 2
    Fabian... Thanks for the formatting. Much easier for others to use. – David G. Varela Aug 8 '17 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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