Change #1190810 had a related patch set uploaded (by Samwilson; author: Samwilson):

[mediawiki/extensions/CommunityRequests@master] Order wishes by utf8mb4 charset rather than binary

https://gerrit.wikimedia.org/r/1190810

The normal MediaWiki way is to use the default collation I think. For example, see here where AWB sorts before AbcMusic.

We can make it work by casting the title e.g. "CONVERT(crt_title USING utf8mb4) but I'm not sure there's a cross-DB way to do that (there's a buildStringCast() method but that casts to binary and is for other types and not for binary to text).

Perhaps there's nothing to be done here?

In T405388#11208759, @Samwilson wrote:

The normal MediaWiki way is to use the default collation I think. For example, see here where AWB sorts before AbcMusic.

We can make it work by casting the title e.g. "CONVERT(crt_title USING utf8mb4) but I'm not sure there's a cross-DB way to do that (there's a buildStringCast() method but that casts to binary and is for other types and not for binary to text).

Perhaps there's nothing to be done here?

Because we have to fetch more results than we need in order to accommodate language fallbacks, we actually do a final round of sorting in PHP. I imagine the fix would need to be applied there.

True, but if the DB sorting is case-sensitive then the PHP sorting won't have the correct pages to sort. It wouldn't make sense to sort the current page of results case-insensitively if the pagination is determined case-sensitively.

Ah yes, you're right! Also I see your WIP patch already does the thing on the PHP-side too, so never mind me :)

Anyway, would using LCASE() on the title sort help at all?

If we have to, I would think it's fine to do RDBMS-specific transformations to the query.

Anyway, would using LCASE() on the title sort help at all?

No, there's no letter case for binary columns, so LCASE doesn't change anything.

We want to use a more human-friendly collation. Perhaps it's correct to do CONVERT(crt_title USING utf8mb4) and then use strnatcasecmp() in PHP to do the re-sorting? I'll update the patch to do that. Converting to lowercase doesn't work for all languages.

Or we could change crt_title to be a text column instead of binary?

Change #1190810 merged by jenkins-bot:

[mediawiki/extensions/CommunityRequests@master] Keep DB sorting for wishes

https://gerrit.wikimedia.org/r/1190810

@Samwilson Please review AC3, thanks!

Test Result - Meta Beta

Status: ✅ PASS ❌ FAIL
Environment: Meta Beta
OS: macOS Tahoe 26.0.1
Browser: Chrome 141
Device: MBA
Emulated Device: NA

Test Artifact(s):

Test Steps

Test Case 1: Verify case-insensitive sorting of wish titles

1. Navigate to the wish index page on https://meta.wikimedia.beta.wmcloud.org/wiki/Community_Wishlist.
2. Click on the Title column header to sort the wishes alphabetically.
3. Observe the order of wishes that begin with both uppercase and lowercase letters.
4. ✅AC1: Confirm that sorting is case-insensitive and the list is ordered alphabetically regardless of title casing (e.g., *apple, banana, WHALE*).

Test Case 2: Verify reverse sorting order (descending)

1. Click the Title column header again to switch to descending order.
2. Observe the order of wish titles.
3. ✅ AC2: Confirm that descending order sorting is also case-insensitive and correctly ordered (e.g., *WHALE, banana, apple*).

See AC1

Test Case 3: Verify persistence of sorting after page reload

1. Refresh the wish index page.
2. Check that the sort order by title remains consistent.
3. ❌ AC3: Confirm that the sorting behavior remains case-insensitive after reload and does not revert to case-sensitive ordering.

When you Refresh, the sorting changes mixed with wrong sorting order and mixed with other language wishes? When sorting, are different language wishes at the end or whatever it's translated to english would be the order?

The above patch didn't actually fix the sorting to be case-insensitive, so I think the tests that are passing are false positives. The change was to not re-sort one page's worth of results after retrieving them from the DB, and so the difference in sorting is that PHP string comparison was differing from DB binary comparison. It still doesn't sort case insensitively, because it appears that there's no simple way to do that and it's not done in other places where things like page titles are sorted in MediaWiki.

Change #1204721 had a related patch set uploaded (by Samwilson; author: Samwilson):

[mediawiki/extensions/CommunityRequests@master] Sort titles case-insensitively

https://gerrit.wikimedia.org/r/1204721

Change #1204721 merged by jenkins-bot:

[mediawiki/extensions/CommunityRequests@master] Sort titles case-insensitively

https://gerrit.wikimedia.org/r/1204721

@GMikesell-WMF Hopefully this is all groovy now!

In T405388#11418100, @Samwilson wrote:

@GMikesell-WMF Hopefully this is all groovy now!

Thanks @Samwilson It does look pretty groovy now! When you refresh, it goes to the default order of when it was created. I did come across a weird ascending/descending for a particular wish which I just created a separate task for T411407: Wish Sorting Bug: One Wish Appears Repeatedly in Ascending Sort and Disappears in Descending Sort. I will close this task since it appears to be fine. Thanks for all your work!