Сортировка magento категорий по алфавиту с помощью Mysql

SORTING MAGENTO CATEGORIES ALPHABETICALLY WITH MYSQL

http://www.paulwhippconsulting.com.au/webdevelopment/50-sorting-magento-categories-alphabetically-with-mysql

So, I can select all my categories;

SELECT entity_id FROM catalog_category_entity;

Now I want to see them in the order I want them so we need to find their name:

Name is likely to be a varchar so I expect the values to be tucked into catalog_category_entity_varchar but I need to know what entity attribute_id I'm dealing with:

There are a bunch of eav_ tables that look like they should help. They are largely empty but eav_attribute can help. I start by taking a look at the attributes for our category entity_type_id:

mysql> SELECT attribute_code, attribute_id FROM 
 eav_attribute WHERE entity_type_id = 4;
+----------------------------+--------------+
| attribute_code             | attribute_id |
+----------------------------+--------------+
| bottle_size                |          525 | 
| category_ids               |           95 | 
| color                      |           80 | 
| cost                       |           68 | 
| created_at                 |          102 | 
| custom_design              |           90 | 
| custom_design_from         |           91 | 
| custom_design_to           |           92 | 
| custom_layout_update       |           93 | 
| description                |           61 | 
| enable_googlecheckout      |          477 | 
| gallery                    |           83 | 
| gift_message_available     |          497 | 
| has_options                |           98 | 
| image                      |           74 | 
| image_label                |           99 | 
| links_exist                |          506 | 
| links_purchased_separately |          503 | 
| links_title                |          505 | 
| manufacturer               |           70 | 
| media_gallery              |           77 | 
| meta_description           |           73 | 
| meta_keyword               |           72 | 
| meta_title                 |           71 | 
| minimal_price              |           88 | 
| name                       |           60 | 
| news_from_date             |           81 | 
| news_to_date               |           82 | 
| old_id                     |           78 | 
| options_container          |           96 | 
| page_layout                |           94 | 
| price                      |           64 | 
| price_type                 |          498 | 
| price_view                 |          501 | 
| required_options           |           97 | 
| samples_title              |          504 | 
| shipment_type              |          502 | 
| short_description          |           62 | 
| sku                        |           63 | 
| sku_type                   |          499 | 
| small_image                |           75 | 
| small_image_label          |          100 | 
| special_from_date          |           66 | 
| special_price              |           65 | 
| special_to_date            |           67 | 
| status                     |           84 | 
| tax_class_id               |           85 | 
| thumbnail                  |           76 | 
| thumbnail_label            |          101 | 
| tier_price                 |           79 | 
| updated_at                 |          103 | 
| url_key                    |           86 | 
| url_path                   |           87 | 
| visibility                 |           89 | 
| weight                     |           69 | 
| weight_type                |          500 | 
+----------------------------+--------------+
56 rows in set (0.00 sec)

OK, that looks like the category attributes and I can see the one I want. The name attribute has an ID of 33 which I can use to fish the names out of the catalog_category_entity_varchar. Now I need to find their existing order. The position attribute is not used. Instead Magento uses the position column in catalog_category_entity (which is the right place for it because it has a distinct value for each category):

SELECT
                e.entity_id AS 'entity_id', 
                vn.value AS 'name',
                e.position AS 'position'
        FROM 
                catalog_category_entity e 
                LEFT JOIN catalog_category_entity_varchar vn 
                        ON e.entity_id = vn.entity_id AND
                           vn.attribute_id = 33
        ORDER BY vn.value;

in my way



DROP TABLE IF EXISTS CCE_NEW_POSITION;
CREATE TEMPORARY TABLE CCE_NEW_POSITION
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   entity_id INT,
  name varchar(255),
   old_position INT
);

INSERT INTO CCE_NEW_POSITION (entity_id, name, old_position   ) SELECT e.entity_id AS  'entity_id', vn.value AS  'name',
                  e.position AS 'old_position'
FROM catalog_category_entity e
LEFT JOIN catalog_category_entity_varchar vn ON e.entity_id = vn.entity_id
AND vn.attribute_id =41
WHERE e.parent_id =4
ORDER BY vn.value;

UPDATE
                catalog_category_entity e
                LEFT JOIN CCE_NEW_POSITION np
                    ON e.entity_id = np.entity_id
        SET
                e.position = np.id;

DROP TABLE IF EXISTS CCE_NEW_POSITION;