Revisions to How to list all disabled products with sql?

formatting

Source Link

edit approved Jul 5, 2016 at 7:13

103
3

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

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

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

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

added 4 characters in body

Source Link

edited Sep 14, 2015 at 13:24

7ochem

7.6k
14
52
80

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 SELECT `e`.*, IF(at_status ON (.value_id > 0, at_status.entity_id = e.entity_id) AND (.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.attribute_id = '96') AND (.value_id > 0, at_status.store_id.value, at_status_default.value) = '2') = 1) WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.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')

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

Source Link

created Sep 14, 2015 at 12:01

Pradeep Sanku

9.3k
2
41
74

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

Stack Exchange Network

Return to Answer