Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
create the following procedure, then to call it: type "call sort_categories;" in mysql.
delimiter ;;
drop procedure if exists sort_categories;;
create procedure sort_categories()
begin
DECLARE cur_id BIGINT UNSIGNED;
DECLARE cur_eof INT DEFAULT FALSE;
DECLARE cur CURSOR FOR
SELECT entity_id
FROM catalog_category_entity_varchar
WHERE attribute_id = (
SELECT attribute_id
FROM eav_attribute
WHERE attribute_code = 'name'
AND entity_type_id = (
SELECT entity_type_id
FROM eav_entity_type
WHERE entity_type_code = 'catalog_category'
)
)
ORDER BY value ASC
;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cur_eof = TRUE;
SET @i:=0;
OPEN cur;
runForEach: LOOP
FETCH cur INTO cur_id;
IF cur_eof THEN
LEAVE runForEach;
END IF;
SET @i:=@i+1;
UPDATE catalog_category_entity SET position = @i WHERE entity_id = cur_id;
SELECT @i;
END LOOP runForEach;
CLOSE cur;
end
;;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.