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.

A Full Guide to Make Mac Environment for any…

Mac is Shipped with some predefined tools for US PHP – will Depreciated in next MacOS release Apache You can check with Install Http...
Shuvankar Paul
6 min read

Disable Magento 2 Two Factor Authentication

In new Magento Version by Default Magento 2 Enable Two Factor Authentication, but if you work on localhost, then it is not useful for...
Shuvankar Paul
12 sec read

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

Leave a Reply

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