Shuvankar Paul Software Engineer, Strativ AB Magento eCommerce Website Development, Website Development Using Php and HTML and Cross-Platform application development using Appcelerator.

Magento 2 Common Manual MySQL Query

54 sec read

Getting Number of Active Product in a Category

First you need to get the attribute_id of status & visibility (usually it should be 97, 99 but can be different)

Getting Status Attribute ID

SELECT `attribute_id` FROM eav_attribute where entity_type_id = '4' AND attribute_code = 'status'

Getting Visibility Attribute ID

SELECT `attribute_id` FROM eav_attribute where entity_type_id = 4 AND attribute_code = 'visibility'

SELECT
	count(CCP.category_id) AS total_products
FROM
	catalog_category_product AS CCP
	INNER JOIN cataloginventory_stock_item AS CSI ON (CCP.product_id = CSI.product_id)
		AND(CSI.is_in_stock = 1)
		INNER JOIN catalog_product_entity_int AS CPEI ON CCP.product_id = CPEI.entity_id
			AND(CPEI.attribute_id = {status_attribute_id})
			AND(CPEI.value = 1)
		LEFT JOIN catalog_product_entity_int AS CPEI2 ON CCP.product_id = CPEI2.entity_id
			AND(CPEI2.attribute_id = {visibility_attribute_id})
			AND(CPEI2.value = 4)
	WHERE
		CCP.category_id = category_id

SELECT
	CCP.category_id,
	CCP.product_id,
	CSI.is_in_stock,
	CPEI.value AS status,
	CPEI2.value AS visibility
FROM
	catalog_category_product AS CCP
	INNER JOIN cataloginventory_stock_item AS CSI ON (CCP.product_id = CSI.product_id)
		AND(CSI.is_in_stock = 1)
		INNER JOIN catalog_product_entity_int AS CPEI ON CCP.product_id = CPEI.entity_id
			AND(CPEI.attribute_id = {status_attribute_id})
			AND(CPEI.value = 1)
		LEFT JOIN catalog_product_entity_int AS CPEI2 ON CCP.product_id = CPEI2.entity_id
			AND(CPEI2.attribute_id = {visibility_attribute_id})
			AND(CPEI2.value = 4)
	WHERE
		CCP.category_id = {category_id}
Shuvankar Paul Software Engineer, Strativ AB Magento eCommerce Website Development, Website Development Using Php and HTML and Cross-Platform application development using Appcelerator.

Magento 2 Module etc/module.xml What,Why & How. Declaration Format…

This is the First Step of Creating new Magento 2 Module.This is called Module Declaration module.xml Location In your Module, for example VendorName_ComponentName ModuleCreate...
Shuvankar Paul
1 min read

Bulk Replace Product SKU in Magento 2

The below code and content is tested in Magento 2.3.X Sometimes you need to update the SKU in bulk process, but I will recommend...
Shuvankar Paul
55 sec read

Leave a Reply

Your email address will not be published. Required fields are marked *