SQL query to find what products do not have status set

Question

For some reason some products we have entered using a custom product importer have not had status set, and as such we cannot find them in the backend but they are there in the database.

I want to find an SQL query that will give me all product entity ids where no status has been set.

Simplification of catalog_product_entity:

| entity_id | ... |
| 988       | ... |
| 999       | ... |
| 1000      | ... |

Simplification of catalog_product_entity_int:

| entity_id | attribute_id | value | ... |
| 988       | 96           | 1     | ... |
| 999       | 12           | 0     | ... |
| 1000      | 96           | 2     | ... |
| ...       | ...          | ..    | ... |

So in that example, products 988 and 1000 have an entry for attribute_id= 96 (which is the attribute_id for status), but 999 does not. It is the ID 999 I want to find with my SQL query.

SELECT `catalog_product_entity`.`entity_id`, `catalog_product_entity_int`.`attribute_id`
FROM `catalog_product_entity`
LEFT JOIN `catalog_product_entity_int`
ON (`catalog_product_entity`.`entity_id` = `catalog_product_entity_int`.`entity_id`)
WHERE `catalog_product_entity_int`.`attribute_id`=96
AND `catalog_product_entity`.`entity_id` IS NULL

But this query is not working correctly because the row does not exist for the product in the first place.

Could someone help me out with another query? I know this is basic SQL but I thought it might be helpful for other Magento users trying to find how status is not set.

I have also tried to get the products using the Magento collection method (->addAttributeToFilter('status',array('null' => true))) but this does not work, again because I think the status record has not been created for the product.

If I can get the IDs with a direct SQL query, I will then load them the 'Magento' way and resave them with a status.

Thanks.

Answer 1

We think we have found a solution:

SELECT `catalog_product_entity`.`entity_id`
FROM `catalog_product_entity`
WHERE `catalog_product_entity`.`entity_id`
NOT IN (
    SELECT `entity_id`
    FROM `catalog_product_entity_int`
    WHERE `catalog_product_entity_int`.`attribute_id`=96
)

Answer 2

The collection won't work for finding missing records. You should really do a LEFT JOIN to find products with missing (or incorrectly set since you used a custom importer) status data.

SELECT p.`entity_id`, p.`sku`, i.`value`
FROM `catalog_product_entity` AS p
    LEFT JOIN `catalog_product_entity_int` AS i
        ON p.`entity_id` = i.`entity_id`
WHERE i.`store_id` = 0
    AND i.`attribute_id` = 96 -- status attribute ID
    AND i.`value` IS NULL; -- You can customize this to find other status values

Answer 3

If you we're to look for missing status records in the catalog_product_entity_int table, the following query would do the trick:

SELECT cpe.entity_id
    FROM catalog_product_entity AS cpe
        LEFT JOIN catalog_product_entity_int AS cpei 
            ON cpe.entity_id IN ( cpei.entity_id ) AND cpei.entity_type_id = '4' 
            AND cpei.attribute_id = '96' AND cpei.store_id = 0
WHERE cpei.value IS NULL

This will return a list of entity_ids which are present in the catalog_product_entity table, but do not have a status attribute associated. One could then use the following query to insert the missing records for the default store_id '0':

( However, always first check if the actual "status" attribute_id is '96' !! )

INSERT INTO catalog_product_entity_int (
    entity_type_id,
    attribute_id,
    store_id,
    entity_id,
    value
)

    SELECT 4, 96, 0, cpe.entity_id, 1
        FROM catalog_product_entity AS cpe
            LEFT JOIN catalog_product_entity_int AS cpei 
                ON cpe.entity_id IN ( cpei.entity_id ) AND cpei.entity_type_id = '4' 
                AND cpei.attribute_id = '96' AND cpei.store_id = 0
    WHERE cpei.value IS NULL;