Magento tips: Display a Magento category path in SQL

Yes, it’s been quite a long time I’ve not been sharing something with you: many projects to handle and no time to share something. And when we are busy, it’s a bit disappointing to see how many time we may lost to do some repetitive tasks

One of these tasks, when you study a Magento sales catalog, is to find in which categories is published a product: if you go to the back-office product sheet, you’ll see the related categories, but you need to open all the directory tree if you have not linked a product to the two main levels.

A really boring job when you have many levels in your sales catalog…

One of the faster ways to find where a product is sold, is to look in the catalog_category_product table (this table contains for each categories the related linked products). Nice but not enough: the Magento category directory tree is managed with the path value referencing each categories identitfier involved in its path. If you want to find their name, you’ll have to find for each category id its name attribute value. Also boring

To avoid this lost of time, I’ve made a SQL function which displays for a category id its path; just call the MAGE_GET_CATEGORY_PATH method to find it. This method expects two parameters: the category id to look for its path, and the store id

SELECT MAGE_GET_CATEGORY_PATH(1628, 8);
+-------------------------------------------------+
| MAGE_GET_CATEGORY_PATH(1628, 8)                 |
+-------------------------------------------------+
| Root Catalog/Store_Root_Category/Women/Handbags |
+-------------------------------------------------+

If you want this method, here’s the SQL script to execute on your Magento database:

delimiter;
######################################
## remove possible existing functions
######################################
DROP FUNCTION IF EXISTS SPLIT_STR;
DROP FUNCTION IF EXISTS COUNT_OCCUR;
DROP FUNCTION IF EXISTS MAGE_GET_CATEGORY_PATH;
delimiter |
#############################################################################
# create function which is able to split a string according with a delimiter
#
# @param x the string to split
# @param delim the delimiter to use to spllit
# @param pos the occurence we look for in string
#############################################################################
CREATE FUNCTION SPLIT_STR(x VARCHAR(255), delim VARCHAR(12),pos INT)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');
|
##############################################################################
# function which count number of occurence of a delimiter exist in a string
#
# @param stringToTest the string where look for element
# @param delim the delimiter to use to spllit
##############################################################################
CREATE FUNCTION COUNT_OCCUR(stringToTest VARCHAR(255), delim VARCHAR (255))
 RETURNS INT
BEGIN
RETURN  LENGTH(stringToTest) - LENGTH(REPLACE(stringToTest, delim, ''));
END;
|

###############################################################################
# function which retrieve a category path from the entity id for a store
#
# This method split each part of the path to fetch name value, and return the concatened value
# @author Matthieu MARY
# @param catId the category id to look for
# @param storeId related store Id
###############################################################################
CREATE FUNCTION MAGE_GET_CATEGORY_PATH(catId INT, storeId INT) 
 RETURNS TEXT
DETERMINISTIC
BEGIN
	DECLARE pathFound TEXT Default '';
	DECLARE categoryPath TEXT Default '';
	DECLARE pathOcurrences INT UNSIGNED Default 0;
	DECLARE counter INT UNSIGNED Default 0;
	DECLARE pathPart VARCHAR(255);
	DECLARE currentEntityId INT UNSIGNED Default 0;
	SELECT path FROM catalog_category_entity WHERE entity_id = catId INTO @categoryPath;
	SELECT attribute_id FROM eav_attribute WHERE attribute_code = 'name' AND entity_type_id = 3 INTO @attributeId;
	SELECT COUNT_OCCUR(@categoryPath, '/') INTO @pathOcurrences;
	WHILE counter <= @pathOcurrences DO	
	 	 SET counter=counter+1;
		 SELECT SPLIT_STR(@categoryPath, '/', counter) INTO @currentEntityId;
		 SELECT IF(t_s.value IS NULL, t_d.value, t_s.value) 
			FROM catalog_category_entity_varchar AS t_d 
			LEFT JOIN catalog_category_entity_varchar AS t_s ON (t_s.attribute_id = @attributeId AND t_s.store_id = storeId AND t_s.entity_id = @currentEntityId)  
			WHERE t_d.attribute_id = @attributeId AND t_d.store_id = 0 AND t_d.entity_id = @currentEntityId INTO @pathPart;
		 SET pathFound = CONCAT(pathFound, @pathPart, '/'); 
	 end while;	
	RETURN  pathFound;
END;
|
delimiter ;
##########################################################################################
# test case: just update category id and store id according with your own catalog content
##########################################################################################
SELECT MAGE_GET_CATEGORY_PATH(1628, 0);

You can also download it here; do not launch it under phpmyadmin: changing delimiter does not work; use mysql command line instead

Now, you do not spend your time in fetching category path information and have more time for facebook, twitter or posting blog posts 🙂