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.