1

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.

2

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
)
  • If it is the solution you can mark your answer as the correct answer – Klettseb Jun 23 '16 at 9:15
0

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
  • Sorry but I don't see how this is fundamentally any different to the SQL query I posted in my question (apart from the abbreviations). It still does not find products where status is not set at all (which is not the same as them being set as NULL). I did find a solution which I posted in my answer. Thanks for your response. – Sarah Jul 5 '16 at 19:56
0

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;

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.