Category ordered by alphanumerical characters

[MichaelD]

Hi,

I have the following problem: I've set the category ordering in 'categories view' to 'Title ascending'. It is working fine as long as no numbers are involved. But I have many categories that have the same alphabetic prefix, but followed by a unique number. The ordering happens according to the numbers. However, the application doesn't recognize the numbers as a single figure, but as distinct numbers. The categories are ordered accordingly. It looks like this:

Ausf 25
Ausf 26
Ausf 276
Ausf 3
Ausf 33
Ausf 36
Ausf 4
(etc.)

I suppose this a database issue, but I'm too inexperienced to solve this on my own. I would appreciate if you could point me in the right direction, thanks!

[Jan]

Hi, yes, this depends on database settings. Unfortunately, I don't have any experiences regarding changing ordering behaviour in database, so hard to give any clue advice

Jan

[MichaelD]

Hi Jan, thank you for looking into my question.

I found out that the PHP command NATSORT seems to do the trick of sorting numbers in a natural order. Check here: https://www.php.net/manual/en/function.natsort.php

I wonder if you could point me to the file where categories are sorted, so that I can give it a try. Thanks in advance.

[Jan]

The question is, if this should be done on database layer or on PHP. Mostly this should be solved on database layer. In Phoca Gallery there is no system of ordering based on PHP, it just asks database and ask directly with ordering instruction. Then the ordering is not changed.

The model for categories view is here:
components/com_phocagallery/models/categories.php

Jan

[MichaelD]

I didn't find a way to influence the sorting mechanism on a front end file. As the sorting in the backend is the same, I suspect that the sorting algorhythm is buried in one of the backend files in /administrator. Perhaps in
...\administrator\components\com_phocagallery\libraries\phocagallery\ordering\ordering.php?

For those having the same problem: I seem to have found a workaround:
- Go to 'Options' --> 'Categories view'
- Set the category odering to 'Ordering Ascending'
- Then go to the listing of your categories, set the ordering to 'Ordering Ascending' as well.
- You now may move the categories around as you please (one by one). They will be displayed on the front end the same way you have ordered them on the backend.

This is a very tiresome procedure if you have many categories as I do, but it's better than nothing I guess...

[Jan]

Hi, the ordering function just add the ordering string which is set in database query (so you can enable debug mode and see the queries which are run there). So there is no specific magic function in PHP, it just ask the database "order by title DESC" or "order by title ASC" and then it depends on the database how the returned output will be ordered.


Unfortunately, in PHP there is not possible to somehow change the "order by title ASC/DESC" to some other SQL command to order different way.

Jan