Listing pods, not sorted alphabetically with danish letters…

Hi @oldrup,

What value are you using for sorting?

I just did a simple PHP array sort test and it seems it takes these characters into account the proper way.

So, if you sort by the post title is should be ok.

Regards, Jory

Bjarne, your page is sorting in Slug-order, it seems, not in Title order.
Since you in the slugs represent the Danish characters the usual way as ? => AE, ? => OE, ? =>AA, you will automatically see ‘Aviser’ (slug: aviser) and ‘?ggebakker’ (slug: aeggebakker) next to each other. Hey, Newspapers and Eggcartons are a little related, huh?

Jory, the three Danish extra characters in the alphabet are represented by odd values in all character sets, out of sort order. So unless you had set your PHP array sort to a correct Danish locale before testing or maybe were using the special asort in the Collation class, a simple array_sort would never have sorted them out correctly.

That said, if the local WordPress running is fully Localized, I assume that WordPress should sort out correctly using the post-titles, even if Pods is not fully localized. Meaning that a WordPress query might represent them correctly, even if using Pods directly do not.
Can’t really say for sure. I have never used a Danish version of WordPress.
But to my knowledge Pods is not fully localized/internationalized (yet).

Assuming that the sorting is ACTUALLY on titles, and not on the slugs as it seems, to know what is going on here, one would have to know the specific code doing the sorting on the page.

What is actually sorting the post-titles in this page? WordPress, Pods code, or is the sorting simply in the order of the MySQL query results and its sorting? To sort results correctly on it’s own, the MySQL installation have to be running the right character-set and collation as well.

The fact that the posts happen to be saved as Pods (Custom Post Types) should be irrelevant. What matters is the localization of the code that actually sorts this list.

In Danish the alphabet goes …, X, Y, Z, ?, ?, ?.

Been battling those darn three characters my whole life.
My (old) middle name has an ‘?’ in it. ??

The old joke goes. Try to make an American say “R?dgr?d Med Fl?de”. ??
Guaranteed to make him tongue-tied. ??

Sorry for the late response, busy working on other matters ??

The code used to display the list, is the following:

<ul>
[pods name="materiale" orderby="title" limit="300" where="genbrugsplads.meta_value=1" template="li-materiale"]
</ul>

Am I missing something?

The site language is set to Danish, WordPress 4.8.1 and language files are updated.

Additional info. If I create regular WordPress posts with the following titles:

Aviser
?ggebakker
Batterier

They are sorted that way, instead of the proper order:

Aviser
Batterier
?ggebakker

Which my indicate, that this is an issue with my WordPress configuration in general ?? Any idea how to resolve this would still be appreciated though.

On your first response, the pods direct example.

Pods simply transfer this into a MySQL query. Your ‘orderby’ spec translates into an ‘ORDER BY’ clause on the SELECT statement sent to MySQL. No further sorting is done by pods or even WordPress itself.
So, for that (and many other things) to work, your MySQL tables have to be set up to sort correctly in Danish. The Character set and collation method have to have been set correctly.

That same thing goes for your second response. Sorting by ‘post_title’ will only work if the system is setup correctly. Preferably from the very beginning, before you start adding data.

Selecting “Danish” as the WordPress language unfortunately simply makes WordPress pick a specific set of language translation files. Suddenly renaming “Web Site” to “Websted”, “Papirkurv” instead of “Trash” and similar. It makes WordPress speak Danish. BUT.. It does not magically change your databases, the characterset it uses, or the collation method. Those are set deeper, and again, the defaults to use when creating tables should have been chosen BEFORE actually creating tables.

The default MySQL characterset and collation is defined when MySQL is installed. Set in it’s configuration. But those MySQL defaults are only used, when the creator of the table (here WordPress) do not specify something on their own when they issue the CREATE TABLE.

If you look at your post table in MySQL/phpMyAdmin, you might see that the table has been created using something like characterset ‘utf8’ or if starting on later versions of WordPress ‘utf8mb4’, collation “utf8_general_ci”.
utf8_general_ci as a collation will not sort Danish or any other language with special characters correctly. The collation would be something like ‘utf8_danish_ci” to come out from MySQL correctly.

In your wp-config.php file, you would see lines like these (these are the defaults):

define(‘DB_CHARSET’, ‘utf8’); // (Or utf8mb4)
define(‘DB_COLLATE’, ”);

They are used on table creation (during WP install) to define the correct table characterset AND Collation.
They SHOULD have been defined as I described above (with a Danish collation) to get a correct result.

In your case, your tables already exist, have already been set up. So, they are likely not speaking or collating correct Danish. ?? Only way to get MySQL to change is to change/convert the tables using ALTER TABLE.. Mind you, this will most likely be a hassle, because ALTER TABLE refuses to change the collation with incorrect data in the table (even for irrelevant stuff such as date default formats).. So ALTER TABLE at times flunks out repeatedly complaining about validations failing, because of goofy stuff WordPress has stuck into the tables.. Like using now invalid defaults on date fields, and using zero value dates on WordPress (auto-)drafts, …
So the path to completing the ALTER TABLE for collation can be a long one, where you first must fix the bad data and table field defaults, before the collation change can finally happen.

You could be in for somewhat of a project. Either for you of someone with more experience. This is not something that can be handled through a Pods support group, since it is very local, very MySQL, and very WordPress installation specific. Not really Pods at all.

The best would of course have been if all this was setup correctly and collations tested to work on initial install.. It it is much harder to fix after the fact, when the data and config is already present and have to be “fixed”.

Notice, though, that you have other measures that can fix sorting on pages.
There are many ways to skin a cat.

One is to simply ignore the MySQL sorting and doing it “yourself” in the code.
For example, my short-term (or maybe even permanent) fix for this would have been to add a routine that could correctly sort data using the PHP Collation class, and then simply sort it on the way out. Run the Pods select, get the data without caring about how it is sorted, and then “Danish sort” it in PHP before outputting the page code. In effect hiding the fact that the DB actually returns things out of order, when viewed from a Danish perspective.

Mind you, that means you cannot use Pods Templating or Pods Short-codes in pages of course. You would have to put a little more work into it. ??
My opinion stems from me not using Pods Templates and short-codes at all. I use Pods code/MySQL, and WordPress templating all the way.

Hvordan er vejret i lille Danmark for tiden? Stadig sommer eller er efteraaret paa vej?
Hvor jeg er lige nu er efteraaret ved at anmelde sig. Eftermiddags temperaturer begynder at droppe under 35 grader.

OK.. Assuming that you do not want the BIG conversion project, I just tried to switch my test environment to Danish and create some test Danish posts and pods. Some ?ggebakker posts and such. ??

Of course WordPress failed miserably in sorting these. ??

So, since default MySQL/WordPress is doing it wrong, I did this to teach the pair to sort.

MySql can be told to use a collation different from it’s DB/table configuration on individual SELECT statements. By using the COLLATE clause.

Since WordPress unfortunately does not do this, even after a correctly set DB_COLLATE in your wp-config, you can add the below code to your theme’s functions.php (I am hoping you are using a child-theme as one should always do?? ?? )

First set DB_COLLATE in your wp-config to ‘utf8mb4_danish_ci’.

then add the below to the theme functions.php

function fix_Collation($query) {
    if (defined('DB_COLLATE')) {
	$col = DB_COLLATE;
	if ( preg_match( '/^\s*(select).+(ASC|DESC)\s/i', $query ) ) {
	    $query = str_ireplace(['ASC', 'DESC'], ['COLLATE ' . $col . ' ASC','COLLATE ' . $col . ' DESC'], $query);
	} 
    }
    return $query;
}

What the filter does is catch all the DB queries going through the system, and when it sees a SELECT … ORDER BY type statement, it adds a Danish collation to it before it can be handed to MySQL for execution.

That fixed my WordPress to act very nice when you speak Danish to it. ??
Sorting by title in various places such as ‘Alle Indl?g’ now knows where to put all my ?ggebakker and ?ld?ser. ??

NOTICE, my database character set and collation is utf8mb4 .
If your database is running the older WordPress setup with ‘utf8’ characterset, you will need to replace the utf8mb4_danish_ci in the above with ‘utf8_danish_ci’.
utf8 and utf8mb4_danish_ci do NOT work together.

It is a little simplistic, since I only had a few minutes, but it works without fail in my test. There is no guarantee, though, that some plugin or setup could not generate a SELECT syntax where my simple replacement logic could create invalid SELECT syntax by sticking the COLLATE in some places mySQL don’t want it. ??
Just keep that in mind if you ever start seeing SELECT syntax errors in the future, and do some fixing. ??

But.. Try it out and see if it takes care of your issue. If so, it is a 1 minute fix.

@jory, and @caleb, thank you so much, for pointing me in the right direction, I had no idea that post sorting was affected by how the tables were created initially.

I created a backup of my dev site, and in phpMyAdmin, edited the structure of the wp_posts table, changing character set (sorting) of the post_title field to utf8mb4_danish_cl.. That is, I press the buttons, and phpMyAdmin was friendly enough to generate the required code for me:
ALTER TABLEwp_postsCHANGEpost_titlepost_titleTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_danish_ci NOT NULL;

Now my “?ggebakker” was properly listed last and etc.

Not sure if the fact that I only changed character and collate of post_title and not everything else, will have any unforeseen side effects, but I have a good backup, and will continue testing…

Thanks again so much for you help Caleb, I really learned something today ??

“Vi har haft 5 “metrologiske somre” i ?r, dvs dage med temperaturer over 25 grader. Det er skyet og regner, ikke HVER dag, men n?sten ?? Hvor flytter man hen og koder WordPress i bedre vejr?”

Med venlig hilsen / Kind regards
Bjarne

Changing collation only on post_title is OK, and looking at your site it probably fixes 99.9% of your issues. Doing only one field, and not the whole table probably also saves you some of all the heart-aches I mentioned above. ??

However, if you in the future have other pods or post items such as post/pods Categories, Taxonomies, or tags where there could be Danish characters, then they will of course not be fixed by this. You would either have to fix those individually or make a more global change.

At least you avoided having to add my goofy, global filter code. ??

Hvor rejser man til? Der er mange muligheder. ??
Jeg rejser typisk rundt i USA hele aaaret for at fange de mest behagelige temperaturer og besoege sceniske steder. Til toppen af bjerge i Colorado, besoege Glacier National Park i Montana, Yellowstone, run the Pacific coast US-1… Hvad jeg nu har lyst til det aar.

Lige nu har jeg “siddet stille” en god tid i staten New Mexico. Omkring 30 miles fra graensen til (originale) Mexico.