07 3103 2894

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.

TrackBack URI for this entry

Comments (13)

Subscribe to this comment's feed

Thanks!
written by Peter Kingsbury, April 06, 2010

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

• 
• +0
• 
• 

...
written by DCA, April 23, 2010

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!

• 
• +0
• 
• 

Listing all products in a category
written by Paul Whipp, April 23, 2010

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.

• 
• +1
• 
• 

Updating product descriptions/titles using SQL is it safe?
written by Pete, July 15, 2010

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.

• 
• +0
• 
• 

Updating ...varchar (entity value tables) is generally safe...ish
written by Paul Whipp, July 16, 2010

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.

• 
• +0
• 
• 

Thanks for the response
written by Pete, July 16, 2010

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

• 
• +0
• 
• 

...
written by Paul Whipp, July 16, 2010

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.

• 
• +0
• 
• 

It works
written by Ryan, December 12, 2010

It works, thank you so much mate!b

• 
• +0
• 
• 

Brilliant Solution - For Sorting Thousands of Printer Categories
written by John, March 18, 2011

Paul - Your solution has just saved me hours and hours of manually sorting thousands of categories.

Many thanks, John

• 
• +0
• 
• 

Thanks for the code
written by Rajesh, May 26, 2011

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

• 
• +1
• 
• 

Name attribute id
written by Ian Ryan, December 09, 2011

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'?

• 
• +0
• 
• 

Alphabetic sort of category names in magento
written by Ian Ryan, December 09, 2011

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

• 
• +0
• 
• 

...
written by Paul Whipp, December 14, 2011

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.

• 
• +0
• 
• 

Name

Email

Website

Title

Comment

smaller | bigger

Subscribe via email (Registered users only)

I have read and agree to the Terms of Usage.

Write the displayed characters

Add Comment