Sorting Magento Categories Alphabetically with MySQL

Magento (1.4.01) uses an EAV model which makes its database potentially confusing. EAV is very efficient for sparse matrix applications but is probably a case of the Magento developers being too smart for their own good when it comes to an ecommerce solution. Magento's EAV solution is also somewhat of a half way house, probably because not all of their developers saw things the same way. This can make things doubly confusing.

Generally it is best to use the Magento php classes and structure to manipulate the database both in terms of import and export but sometimes its easier (or just more fun) to get your hands dirty and dig into the database.

Here is an example (I'll welcome feedback on the easier way to do this using the existing Magento classes/importer/exporter):

I need to re-order all of the magento product categories so that they are sorted alphabetically by name. Googling shows that this is a known issue and not something that has a ready solution.

First I need to find the category attributes and verify which attribute is the name I want to sort on and which attribute (or ordered field) determines the category sort order. So I peruse the database...

The catalog_category_entity table has the ids for the categories (entity_id) their entity type (entity_type_id - as they are categories this is always 3 making this a pretty daft column) and their parent category id. It also includes a path and some other bits and pieces we wont worry about for now.

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;

Note that the position is being handled as relevant to the ordering within a particular level. We probably don't need to worry about this and, as I've backed up the database, I'll try the simple approach first using my general procedure for re-ordering rows:

SET @ordering_inc = 1;
SET @new_ordering = 0;

DROP TABLE IF EXISTS CCE_NEW_POSITION;
CREATE TEMPORARY TABLE CCE_NEW_POSITION
        SELECT
                  e.entity_id AS 'entity_id', 
                  vn.value AS 'name',
                  e.position AS 'old_position',
                  (@new_ordering :=
                   @new_ordering + @ordering_inc) AS 'new_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;

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

If MySQL was well behaved, we could avoid the temporary table but it gets confused if you try to use ordering on joined tables in an update. This works fine and the simple position values don't seem to upset Magento with re-ordering or display so we're all good.


Comments

Peter Kingsbury 6 years, 10 months ago

Thanks!
Daunting, to say the least, for the neophyte Magento administrator. Thanks for posting this!

Link | Reply

DCA 6 years, 10 months ago

Cool tips!
But can you help me with a problem?
If you want retrieve categories from x product id?
Thanks in advanced.

This model is a nightmare for developers!

Link | Reply

Paul Whipp 6 years, 10 months ago

Listing all products in a category
There are easier ways to do this in the magento code by using its supplied models but if you want to use pure sql here is the solution:

SELECT
p.entity_id AS 'product_entity_id',
pvn.value AS 'product_name'
FROM
catalog_product_entity p
JOIN catalog_product_entity_varchar pvn
ON p.entity_id = pvn.entity_id AND pvn.attribute_id = 60
JOIN catalog_category_product_index r
ON r.product_id = p.entity_id
JOIN catalog_category_entity c
ON r.category_id = c.entity_id
JOIN catalog_category_entity_varchar cvn
ON c.entity_id = cvn.entity_id AND cvn.attribute_id = 33
WHERE
cvn.value = "Horses"
ORDER BY pvn.value;

Replace "Horses" with the category name you want to list the products for.

Link | Reply

Pete 6 years, 7 months ago

Updating product descriptions/titles using SQL is it safe?
Hello, trying to find an educated answer to this question, from what I can see (but I am no expert) the magento catalog_product_entity_varchar and catalog_product_entity_text tables contain simple text fields (value) containing product descriptions, URLs and titles. I want to make SEO updates to my products replacing text strings within these tables with a query like

UPDATE catalog_product_entity_varchar SET value = replace(value, "OLD TITLE", "NEW TITLE");

After refreshing caches this seems to work ok, but I want to be sure there are no relational tables that will be impacted by changing these product, title, description and URLs directly in the DB - before I updated 1000s of products!

Perhaps you can give me your thoughts.

Link | Reply

Paul Whipp 6 years, 7 months ago

Updating ...varchar (entity value tables) is generally safe...ish
The table you are updating contains the values for all of the varchar typed attributes associated with products.

It is generally safe from the functional perspective to apply updates to the values here so long as you refresh the caches and rebuild the indexes manually afterwards.

Your update statement should have an appropriate where clause to restrict the updates to values that you really want to change. A replace statement like the one you have will change every string containing "OLD TITLE" and you need to be very sure that this is really what you want to do.

At the very least I would generally restrict such an update to the specific attributes of interest.

In any event be sure to back up the table before making the change and preferably do it on a local copy of the site first.

Link | Reply

Pete 6 years, 7 months ago

Thanks for the response
Thanks for your comments, I appreciate that the SQL statement should be more specific and I will use for product title - EAV Attribute 56 :

UPDATE catalog_product_entity_varchar
SET value = replace(value, "OLD VALUE", "NEW VALUE")
WHERE attribute_id = "56" AND value = "OLD VALUE";

What I have actually have done is exported all the data for each category of products creating an individual SQL query automatically in excel so I can search and replace each product title description in its own SQL query which is also safer than a generic string find/replace.

Product title, description changes take immediate effect in the frontend, the search index needs to be rebuilt for them to appear in search. URL changes take affect after refreshing the catalogue rewrite cache.

Have tested on my dev server without any issues.



Pete

Link | Reply

Paul Whipp 6 years, 7 months ago

Sounds good. You don't need the replace if you are setting the value:

UPDATE catalog_product_entity_varchar
SET value = "NEW VALUE"
WHERE attribute_id = "56" AND value = "OLD VALUE";

should do the trick.

Link | Reply

Ryan 6 years, 2 months ago

It works
It works, thank you so much mate!b

Link | Reply

John 5 years, 11 months ago

Brilliant Solution - For Sorting Thousands of Printer Categories
Paul - Your solution has just saved me hours and hours of manually sorting thousands of categories.

Many thanks, John

Link | Reply

Rajesh 5 years, 9 months ago

Thanks for the code
Hi,
It was very good for sort the category in db level. Thanks for your code.:)

Link | Reply

Ian Ryan 5 years, 2 months ago

Name attribute id
Hi

I am looking for a solution for sorting categories alphabetically in magento and I appreciate your post. Just one questiuon, in your tuitorial above you query eav_attribute for the attribute id for 'name'. You go on to say that the attribute id for 'name' is 33 but your query results show an attribute for 'name' of 60. Am I missing something or is 60 the attribute id for 'name'?

Link | Reply

Ian Ryan 5 years, 2 months ago

Alphabetic sort of category names in magento
Will your solution above for sorting category names alphabetically also work in magento v1.6.1

Link | Reply

Paul Whipp 5 years, 2 months ago

Hi,

The solution will work fine in 1.6.1 but you need to look up the correct attribute ids - these change from upgrade to upgrade because of the EAV model magento uses.

Link | Reply

Sven 4 years, 10 months ago

Id for 1.6.2
Could anybody update the name id from Magento 1.6.2 I cannot find it as a newbee, sorry.
Thanks
Sven

Link | Reply

Dave Hoover 4 years, 10 months ago

Corrected Code Category Sort
Here is the corrected code which answers a few questions posted thus far....the [i]entity_type_id[/i] and [i]name[/i] IDs were wrong...here is the clean SELECT code:

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 = (SELECT attribute_id FROM
eav_attribute WHERE entity_type_id = (SELECT entity_type_id FROM catalog_category_entity limit 1) and attribute_code='name')

ORDER BY vn.value

Modify as needed for the actual cateogy sort code....

Link | Reply

Paul Whipp 4 years, 10 months ago

Thanks Dave,

That fixes the solution for 1.6. For 1.4 the article content is still correct.

Link | Reply

milkfilledandroid 4 years, 7 months ago

i tried this for 1.6.1. I see the positions have changed in catalog_category_entity over their previous values. But despite all cache-clearing and re-indexing the old positions still show on the site. I'm banging my head against the wall now. Any ideas would be appreciated.

Link | Reply

Paul Whipp 4 years, 7 months ago

Sorry to hear about your problem milkfilledandroid (interesting handle). I can't reproduce it. Feel free to contact me directly and we'll see if we can work out what is going on in your particular case.

Link | Reply

Jesse 4 years, 7 months ago

Can this be used for sub categories only?

Link | Reply

Paul Whipp 4 years, 6 months ago

No Jesse, it should work for any category although its generally only sub-categories that are displayed on the front end.

Link | Reply

Donald Hohler 4 years ago

Mr
Paul,

I am a little confused. You say that categories entity_type_id = 3, but you used entity_type_id = 4 in your query. You say that "name" = attribute_id = 33, but your screen shot shows attribute_id = 60.

Just trying to follow along. I need this to work BADLY!

Thanks

Link | Reply

Paul Whipp 4 years ago

Hi Donald,

Sorry about the confusion. The values change from version to version of Magento so you need to check them as per the first part of my article and use the values appropriate for your particular version of Magento.

Link | Reply

Kim Bryant 3 years, 11 months ago

Hi Paul,

first of all, thanks very much for posting this tutorial!

For some reason, it didn't work as-is for me, even though I adjusted the name attribute ID to match my version of Magento.

When I created the temp table, the `new_position` column values just matched the `entity_id` values, so it didn't actually change the position for any of my categories. To fix that problem, I added a step in the middle and re-ordered the `new_position` column with the following lines:

mysql> SET @new_ordering = 0;
mysql> UPDATE CCE_NEW_POSITION SET new_position = (@new_ordering := @new_ordering + @ordering_inc);

After that, I updated the `catalog_category_entity` table using your posted query, and everything turned out as expected. I'm not exactly sure why the ordering didn't go right the first time around, but I wanted to share my solution in case anyone else comes across the same issue!

Link | Reply

Paul Whipp 3 years, 11 months ago

Thanks for including this update Kim. Can you tell us what version of Magento you were working with?

Link | Reply

kevindurant 3 years, 8 months ago

cheap nike shoes from china, china wholesale shoes
Buy Nike Jordan Shoes 2013 for women and men at 70 to 80% discount Cheap jordans for sale from china · Choose cheap jordans retro
cheap NIKE store wholesale shoes, You can find cheap nike sneakers and top quality nike shoes for sale, cheap air jordan,cheap nike air max
http://www.goclothingtrade.com/shop/Jordans-Shoes-1263_p1.html


Buy Nike Jordan Shoes 2013 for women and men at 70 to 80% discount Cheap jordans for sale from china · Choose cheap jordans retro
cheap NIKE store wholesale shoes, You can find cheap nike sneakers and top quality nike shoes for sale

Air Jordan Shoes Online

Discount Louis Vuitton China

Cheap Armani Clothes Shop

Link | Reply

Joanne o 3 years, 5 months ago

Subcategories sorting issue as shown on front end
May you please see how to fix sorting issue od subcategories? It appears subcategories I created (brand names) are being sorting by their assigned category ID's. please see example on www.scentonym.com click on perfume and notice that the subcategories ( brand names) are out of order.

Link | Reply

New Comment

required

required (not published)

optional

Australia: 07 3103 2894

International: +61 410 545 357

Feeds

RSS / Atom