0

How can I get a list of disabled products in magento 2 using sql query?

1

To get entity ids and stores only I used:

SELECT entity_id, store_id FROM catalog_product_entity_int
WHERE value = 2 AND attribute_id = (
  SELECT attribute_id FROM eav_attribute
  WHERE entity_type_id=4 AND attribute_code='status'
)

Value 2 means disabled. Value 1 means enabled.

You can use following query to get also product names:

SELECT t_int.entity_id, t_int.store_id, t_varchar.value FROM catalog_product_entity_int AS t_int
JOIN catalog_product_entity_varchar as t_varchar ON t_varchar.entity_id = t_int.entity_id 
WHERE t_int.value = 2 AND t_int.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='status'
) AND t_varchar.attribute_id = (
    SELECT attribute_id FROM eav_attribute
    WHERE entity_type_id=4 AND attribute_code='name'
)
1
  • the sql result is giving different count of collection with admin backend grid collection , I mean if I will filter in backend grid the result is one count and sql is giving another count. I can say there is no pending indexing processor, still don't understand why count is different. Current magento version is 2.3.3-p1 Jun 24 '20 at 13:46
0

For Magento 2.2x version, use row_id instead of entity_id in the above sql

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.