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

mysql_m2_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 'Default Category' assignment
	SET counter = 3;
	# 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 row_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 row_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 cev.`value` INTO @catName
    			FROM catalog_category_entity_varchar AS cev
    			INNER JOIN catalog_category_entity AS cce ON cce.entity_id = @catId
    			WHERE cev.attribute_id = @attributeId AND cev.row_id = cce.row_id AND cev.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 row_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,
    cce.row_id AS row_id,
    ccev.store_id,
    c.name AS store_name,
    get_category_path(cce.row_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.row_id = cce.row_id
INNER JOIN store AS c ON c.store_id = ccev.store_id
ORDER BY ccev.store_id, cce.entity_id;