18

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/

5 Answers 5

33

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
0
8

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

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));
2
  • 2
    Fabian... Thanks for the formatting. Much easier for others to use. Aug 8, 2017 at 14:08
  • In my db schema has changed from entity_it --> row_id catalog_product_entity_int.row_id AS row_id
    – Lunf
    Sep 15, 2021 at 0:13
0

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.

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.