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.
Australia: 07 3103 2894
International: +61 410 545 357