Thread

I have a category group that I am displaying:

{exp:channel:categories channel="faq"}
     {category_id} {category_name}
{/exp:channel:categories}

I have the category list set to alphabetical in the control panel and it displays properly in the control panel, but not in my template.  In my template the categories appear to be sorted by category_id.  If I change to custom order in the control panel the custom order seems to display properly. 

I am on EE 2.1.0 20100805

Thanks!

Guy

Guy,

Under Admin > System Administration > Database Settings do you have SQL Query Caching enabled? Try clearing all EE caches from from Control Panel.

Brandon,

I am not SQL caching and I cleared the caches - the problem persists.  Any more thoughts?

Custom ordering works, but alphabetical produces results were they are sorted by the category id in the template (fine in the control panel).

Guy

Guy,

I cannot replicate this I’m afraid.

I even used your reduced example. Is this how you are testing by just adding your category tags into a blank template and nothing else?

Do you have any template caching set?

It would also be worth upgrading to latest build so we are troubleshooting from same page

John,

No caching and as I said before custom order works and changes made to that are instantly seen.  So I’ve dug a little deeper and solved a few mysteries but still no solution.

1) Why are the categories properly alphabetized on the categories page in the control panel, but not my template?

Answer: I turned on SQL debugging and looked at the queries and it is two different queries.

Here is the query from the control panel page:

SELECT cat_name, cat_id, parent_id, g.group_id, group_name 
                             FROM exp_category_groups g, exp_categories c
                             WHERE g.group_id = c.group_id AND g.group_id IN ('3')
                             ORDER BY group_id, parent_id, cat_name 

This uses the actual category name to put the list in order (grouped of course).

On my template a different query returns the listing:

SELECT DISTINCT(c.cat_id), c.parent_id, c.cat_name, c.cat_url_title, c.cat_image, c.cat_description , cg.field_html_formatting, fd.* 
                    FROM exp_categories AS c
                     LEFT JOIN exp_category_field_data AS fd ON fd.cat_id = c.cat_id
                            LEFT JOIN exp_category_groups AS cg ON cg.group_id = c.group_id
                    WHERE c.group_id IN ('3')  ORDER BY c.group_id, c.parent_id, c.cat_order 

This query uses the c.cat_order column to put the list in alphabetical order.

Looking at the database I made an interesting discovery - when I switch the list the alphabetical order the value of cat_order is set to 1 for all the entries in the group, instead of being properly ordered - hence my unordered listing.  Further digging revealed that when I first create the categories the appropriate values are in the cat_order column, but when I switch from alphabetical to custom and back again the proper order is replaced with a 1.

CAT/GROUP/SITE/PARENT/TITLE/CAT_ORDER
103   1   8   0   alligator         1
102   1   8   0   human         1
101   1   8   0   cat   cat         1
100   1   8   0   dog   dog         1
99   1   8   0   zebra         1
98   1   8   0   bird             1
104   1   8   0   rhino             1

So on the update from custom to alphabetical the cat_order is being written as a 1.

Any thoughts?

Guy

Guy,

Can I get you to upgrade to latest build please. If issue persists we will go in for a look because I cannot replicate on my clean install.

I hope it’s not poor etiquette to jump in, but I’ve encountered the same issue as Guy. I followed the advice given in this thread and just updated to the latest build (EE 2.1.0, 20100810), but the problem persists.

I set up categories alphabetically originally, and when they displayed in cat_id order on my site, I tried switching to custom order to shuffle them, before switching back to alpha. However, my categories continue to display in cat_id order WHILE respecting parent/child relationships. Also like Guy’s case, in Control Panel all categories sort alphabetically and respect parent/child properly.

Following Guy’s lead, I looked at my database and sure enough all of my cat_order are now 1 (unless 1 is the correct value for alphabetical order, this seems to be the problem). Below is the output from my database, and in case it’s helpful in troubleshooting, here is a short explanation of my categories.

My top-level categories are set up as continents. Within continents are countries (as children of continents) and within some countries are regions (as children of countries). I entered most of the categories at once, but forgot a few countries (for example, Switzerland, Czech Republic, Hungary) and had to add them later. That’s when I noticed that the alphabetical sorting wasn’t working.

Here’s the relevant bit of my database. Notice the 1s all the way down the cat_order column.

INSERT INTO `exp_categories` (`cat_id`, `site_id`, `group_id`, `parent_id`, `cat_name`, `cat_url_title`, `cat_description`, `cat_image`, `cat_order`) VALUES
(1, 1, 1, 0, 'Africa', 'africa', '', '', 1),
(2, 1, 1, 0, 'Americas', 'americas', '', '', 1),
(3, 1, 1, 0, 'Antarctica', 'antarctica', '', '', 1),
(4, 1, 1, 0, 'Asia', 'asia', '', '', 1),
(5, 1, 1, 1, 'Morocco', 'morocco', '', '/public_html/images/uploads/flags/flags_gif/morocco.gif', 1),
(7, 1, 1, 1, 'South Africa', 'south_africa', '', '', 1),
(8, 1, 1, 2, 'Argentina', 'argentina', '', '', 1),
(9, 1, 1, 2, 'Brasil', 'brasil', '', '', 1),
(10, 1, 1, 1, 'Tunisia', 'tunisia', '', '', 1),
(11, 1, 1, 2, 'Canada', 'canada', '', '', 1),
(12, 1, 1, 2, 'Chile', 'chile', '', '', 1),
(13, 1, 1, 2, 'Colombia', 'colombia', '', '', 1),
(14, 1, 1, 2, 'Cuba', 'cuba', '', '', 1),
(15, 1, 1, 2, 'Mexico', 'mexico', '', '', 1),
(16, 1, 1, 2, 'United States', 'united_states', '', '', 1),
(17, 1, 1, 2, 'Uruguay', 'uruguay', '', '', 1),
(18, 1, 1, 4, 'China', 'china', '', '', 1),
(19, 1, 1, 4, 'Hong Kong, SAR China', 'hong_kong_sar_china', '', '', 1),
(20, 1, 1, 4, 'Macau, SAR China', 'macau_sar_china', '', '', 1),
(21, 1, 1, 4, 'Japan', 'japan', '', '', 1),
(22, 1, 1, 0, 'Middle East', 'middle_east', '', '', 1),
(23, 1, 1, 22, 'Qatar', 'qatar', '', '', 1),
(24, 1, 1, 0, 'Europe', 'europe', '', '', 1),
(25, 1, 1, 24, 'Austria', 'austria', '', '', 1),
(26, 1, 1, 24, 'Belgium', 'belgium', '', '', 1),
(27, 1, 1, 24, 'Bosnia i Hercegovina', 'bosnia_i_hercegovina', '', '', 1),
(28, 1, 1, 24, 'Bulgaria', 'bulgaria', '', '', 1),
(29, 1, 1, 24, 'Croatia', 'croatia', '', '', 1),
(30, 1, 1, 24, 'France', 'france', '', '', 1),
(31, 1, 1, 24, 'Germany', 'germany', '', '', 1),
(32, 1, 1, 24, 'Gibraltar', 'gibraltar', '', '', 1),
(33, 1, 1, 24, 'Greece', 'greece', '', '', 1),
(34, 1, 1, 24, 'Ireland', 'ireland', '', '', 1),
(35, 1, 1, 24, 'Italy', 'italy', '', '', 1),
(36, 1, 1, 24, 'Luxembourg', 'luxembourg', '', '', 1),
(37, 1, 1, 24, 'Macedonia', 'macedonia', '', '', 1),
(38, 1, 1, 24, 'Netherlands', 'netherlands', '', '', 1),
(39, 1, 1, 24, 'Romania', 'romania', '', '', 1),
(40, 1, 1, 24, 'Montenegro', 'montenegro', '', '', 1),
(41, 1, 1, 24, 'Serbia', 'serbia', '', '', 1),
(42, 1, 1, 24, 'Spain', 'spain', '', '', 1),
(43, 1, 1, 24, 'Turkey', 'turkey', '', '', 1),
(44, 1, 1, 24, 'Unclaimed Europe', 'unclaimed_europe', '', '', 1),
(45, 1, 1, 24, 'United Kingdom', 'united_kingdom', '', '', 1),
(46, 1, 1, 24, 'Switzerland', 'switzerland', '', '', 1),
(47, 1, 1, 24, 'Czech Republic', 'czech_republic', '', '', 1),
(48, 1, 1, 24, 'Hungary', 'hungary', '', '', 1),
(49, 1, 1, 0, 'Oceania', 'oceania', '', '', 1),
(50, 1, 1, 30, 'Paris', 'paris', '', '', 1),
(51, 1, 1, 8, 'Tierra del Fuego', 'tierra_del_fuego', '', '', 1),
(52, 1, 1, 8, 'Buenos Aires', 'buenos_aires', '', '', 1),
(53, 1, 1, 8, 'Mendoza', 'mendoza', '', '', 1),
(54, 1, 1, 28, 'Sofia', 'sofia', '', '', 1),
(55, 1, 1, 28, 'Northwest Bulgaria', 'northwest_bulgaria', '', '', 1),
(56, 1, 1, 28, 'Southwest Bulgaria', 'southwest_bulgaria', '', '', 1),
(57, 1, 1, 28, 'Central Bulgaria', 'central_bulgaria', '', '', 1),
(58, 1, 1, 28, 'Bulgaria''s Black Sea Coast', 'bulgarias_black_sea_coast', '', '', 1),
(59, 1, 1, 29, 'Dubrovnik', 'dubrovnik', '', '', 1),
(60, 1, 1, 29, 'Adriatic islands', 'adriatic_islands', '', '', 1),
(61, 1, 1, 16, 'New York', 'new_york', '', '', 1);

Also, just in case anyone is thinking of recommending it, I don’t want to manually edit the cat_id numbers. I will be adding more regularly, and this has to work at the system level.

I’d love to hear what’s happened with Guy’s categories when he updates to the latest build.

Thanks,
Eric

Quick follow-up. I guess there’s no harm in showing the problem in action. Look at the menu of continents just below the logo here:
http://monkeytravel.ehclients.com/index.php/

You’ll see that the regions nest within countries nesting within continents properly, but the alpha order is wrong. For example, see Europe (Czech Republic, Switzerland, Hungary at bottom of list), or look at the regions within Americas/Argentina.

Hope this helps,
Eric

I too am experiencing a similar issue.  Using the following bit of code, the categories display fine, but not in alphabetical order as would be preferred…

<ul>
  {exp:channel:categories channel="blog" show_empty="no" style="linear"}
    <li><a href="http://{path=blog/index}">{category_name}</a></li>
  {/exp:channel:categories}
</ul>

This can be seen live, on my site (http://www.chriswatts.com/), at the bottom under Blog, then Categories.

Has any progress been made on this issue?

Thanks!

Eric/Chris - sorry you too are having the problem but it is good to know I am not alone.

Eric I have not updated my build yet (but I don’t think it is build related) I wanted to try something else first.  I moved my site to a new server and guess what it works!  Same code, same database, same build of EE and no problem.

So John any thoughts on how why the server might play a role.  I have had other issues that seem to be server related as well (http://ellislab.com/forums/viewreply/789423/).  Could there be a permissions, setup or security cause?

HELP!

Thanks!

Guy

Thanks for the update, Guy,

That indicates there’s some behind-the-scenes caching implemented on that host, likely on the MySQL side, that EE unfortunately has no control over.

Eric and Chris,

Do you have a local development environment where you can test this?

Brandon,

Caching doesn’t make sense - why would caching cause the database to write 1’s for all the orders?

Guy

Hi everyone,

In response to Brandon’s question, I don’t have a testing environment. I’m on Engine Hosting, which I assumed/hoped would be optimized for EE installations. Even if there is a caching issue with some hosts, I think this warrants some investigation, as this is a very basic concept that is not functioning properly.

Thanks,
Eric

I agree I think this needs a little more digging - caching just doesn’t seem to make sense.

Any more thoughts/progress on this issue?  Seems like with a few folks having the issue on different host (one being EngineHosting) that there is something to it.  Chris/Eric you made any progress?

Thanks!

Guy