Magento 2 – Get List of Disabled Products with SQL Query
databasemagento2sql
How can I get a list of disabled products in magento 2 using sql query?
Best Answer
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'
)
For resolved this sub items issue, you need to take those entity_id whose order_item_id's sales_item table's parent_item_id is null.
This two tables have a relation via sales_invoice_item.order_item_id=sales_order_item.item_id
So, can run below query
SELECT * FROM `sales_invoice_item` as invoice_item, sales_order_item as order_item
where invoice_item.order_item_id = order_item.item_id and order_item.parent_item_id is null
and invoice_item.parent_id = [INVOICE_ID]
Magento does not save product URLs completely in database tables. It saves only URL key, so you can get SKU and URL key directly from tables.
SKU is saved in catalog_product_entity table.
By default product's URL key is an attribute which is of type varchar, so its value is saved in catalog_product_entity_varchar table. By default attribute id of product's URL key is 124.
Best Answer
To get entity ids and stores only I used:
Value 2 means disabled. Value 1 means enabled.
You can use following query to get also product names: