MySQL to build full Magento 1 category paths with names by store

mysql_m1_category_builder.sql

# Create two stored functions that will be used
DELIMITER $$
CREATE FUNCTION split_string(stringToSplit VARCHAR(256), sign VARCHAR(12), position INT) RETURNS LONGTEXT
BEGIN
	# Return the requested section of the delimited string
	RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(stringToSplit, sign, position),LENGTH(SUBSTRING_INDEX(stringToSplit, sign, position - 1)) + 1), sign, '');
END$$

CREATE FUNCTION get_category_path(catId INT, storeId INT) RETURNS LONGTEXT
BEGIN
	# Holds the full category path names
	DECLARE catPath LONGTEXT;
	# Counter used to loop through the category path
	DECLARE counter INT DEFAULT 0;
	# Set default store ID for use later
	DECLARE defaultStoreID INT DEFAULT 0;
	# Start with a blank path
	SET catPath = '';
	# Start the counter at position 2 to skip the 'Root Category' assignment
	SET counter = 2;
	# Grab the attribute ID for the catalog category name attribute
	SELECT eav.attribute_id INTO @attributeId FROM eav_attribute AS eav INNER JOIN eav_entity_type AS eet ON eet.entity_type_code = 'catalog_category' AND eav.entity_type_id = eet.entity_type_id WHERE eav.attribute_code = 'name';
	# Retrieve the '/' delimited path of the category
	SELECT path INTO @delimitedPath FROM catalog_category_entity WHERE entity_id = catId;
	# Determine the number of slashes in the path
	SELECT LENGTH(@delimitedPath) - LENGTH(REPLACE(@delimitedPath, '/', '')) + 1 INTO @noOfSlashes;
	IF @noOfSlashes = 1
	THEN
		# Only one ID in path, just return that one
		SELECT `value` INTO catPath FROM catalog_category_entity_varchar WHERE attribute_id = @attributeId AND entity_id = @delimitedPath AND store_id = storeId;
		RETURN catPath;
	ELSE
		WHILE counter <= @noOfSlashes DO
			# Reset the catName variable
			SET @catName = NULL;
			# Get the next category ID from the delimited path
			SELECT SPLIT_STRING(@delimitedPath, '/', counter) INTO @catId;
			# Get the name attribute of this category
			SELECT `value` INTO @catName FROM catalog_category_entity_varchar WHERE attribute_id = @attributeId AND entity_id = @catId AND store_id = storeId;
			IF @catName IS NULL
			THEN
				# Didn't find a name, use the default store ID instead, in case it has not been overridden
				SELECT `value` INTO @catName FROM catalog_category_entity_varchar WHERE attribute_id = @attributeId AND entity_id = @catId AND store_id = defaultStoreId;
			END IF;
			# Add this path to the running path
			SET catPath = CONCAT(catPath, '/', @catName);
			# Increment counter
			SET counter = counter + 1;
		END WHILE;
		# Return the full, delimited path of names, minus the leading '/'
		RETURN SUBSTR(catPath, 2);
	END IF;
END$$
DELIMITER ;

# Build output
SELECT DISTINCT cce.entity_id AS category_id, ccev.store_id, cs.name AS store_name, get_category_path(cce.entity_id, ccev.store_id) AS path_names, SUBSTR(cce.path, 3) AS path_ids FROM catalog_category_entity AS cce INNER JOIN catalog_category_entity_varchar AS ccev ON ccev.entity_id = cce.entity_id INNER JOIN core_store AS cs ON cs.store_id = ccev.store_id ORDER BY ccev.store_id, cce.entity_id;