Fixing Magento Category Order Problems
Recently, one of our clients could not get their product categories to appear in the order set in the Magento Catalog > Manage Categories screen. No matter how they dragged and dropped the categories, the ones near the top would not display as intended on the front end.
How Magento Orders Categories
Categories are displayed in navigation menus based on a field titled "position" in the catalog_category_entity data table. By default, the order is displayed in ascending order. There are other ways of adding code to display categories alphabetically, but by default, the order shown is determined by the position value.
So, why would changing the order under Manage Categories not work?
What I Observed
When working in the Manage Categories menu, the client and I were focused on manipulating the top 2-3 categories, as those were the ones most important to him at the time. We could change their order, but even after re-indexing the Flat Catalog, the front end would still not display the correct order.
Furthermore, after returning to the Manage Categories page, the category order reverted back to the order it was before we changed it to our liking.
While at first, I thought there might be some answer to this among the thousands of Magento-related posts (perhaps we were just missing some step in the process), no one seemed to have experienced this same phenomenon (therefore, this post may have little or no value to anyone). Some had proposed coding changes to sort categories by name; others had modified code in some places to sort by position. However, I knew Magento, by default, already sorted categories by position.
So, I had to try to find a solution myself.
Discovering the Problem
Since display order is determined by the value in the "position" field, I decided to start there to see if anything looked amiss.
I looked at the data in the catalog_category_entity table for all categories at level "2" (root catalogs are level "1"), with the same root catalog path, in this case, "1/3" for Website 1, Root Catalog 3 (the client has 3 root catalogs in their multi-store set-up), I found the position values for the top categories to be: 0,0,0,0,3,4,5,7,11,12,14,17,19,20,21,28,33 when sorting the results by position in ascending order.
How they got this way is not clear, but they were surely not in a clean numerical sequence. Since the first four categories all had the position value of "0," this could well explain why, even when re-ordering, the frontend order was not the same. My suspicion is that when re-ordering categories, Magento tried to re-set the position order, but not by simply clearing out the existing positions at that level and inserting a fresh set of incremental values; it must have some dependence on the values around the category being moved, since moving a category did not, in and of itself, recalibrate the position values.
Re-setting the Category Order
I manually changed the position values to 1,2,3,4, etc. in the data table, and re-indexed the Flat Catalog. It worked. The frontend (and backend) category order was as designated by the position field.
Under Manage Categories I did some tests of dragging and dropping categories into various positions and viewing the results in the data table. Everything seemed fine.
I'm still not sure how the order got out of whack in the first place. Our client is using Magnetic One to manage his Magento store; there could be something in how it interacts with the data tables that upset the order, but I'm not sure (I have read posts where others caution against using third-party management tools due to Magento's complexity: few will get it right). It may have been a harried client, dragging categories without waiting for Magento to fully finish the first process. Whatever the reason, I did get it fixed. For now.
