Issue Bug Accepted Orphaned

#89671Allow for Collation in extbase repository queries

Henrik Jensen 11/13/19, 13:31 updated 01/14/20, 10:10

Description

We are experiencing issues with sorting database results.
We use utf8_unicode_ci as collation in all our databases and string columns.

utf8_unicode_ci do not support the danish characters Æ,Ø,Å.
The character Å as sorted as A – giving us wrong sortings.

For example:
SELECT header FROM `tt_content` ORDER BY header ASC
A is the first letter in danish alphabet
Å is the last letter in danish alphabet
B
C

SELECT header FROM `tt_content` ORDER BY header COLLATE utf8_danish_ci ASC
A is the first letter in danish alphabet
B
C
Å is the last letter in danish alphabet

The easy fix is of course to change the database collation to utf8_danish_ci and get the sortings correct.

But we have quite a few multilanguagal sites using the same db and tables. So it would sort correctly in one language
but not in others and we would very much like to control the sorting from the typo3 side.

The problem: You can´t use COLLATE in Extbase repository queries for sort order using the query object.

So in a extbase repository you can do:

$orderings[‘title’] = QueryInterface::ORDER_DESCENDING;

But this will not work:
$orderings[‘title’] = ‘COLLATE utf8_danish_ci ‘.QueryInterface::ORDER_ASCENDING;

$query->setOrderings(
$orderings
);

https://api.typo3.org/TYPO3_8-7/_typo3_db_query_parser_8php_source.html#l00261
protected function parseOrderings(array $orderings, Qom\SourceInterface $source) {
foreach ($orderings as $propertyName => $order) {
if ($order !== QueryInterface::ORDER_ASCENDING && $order !== QueryInterface::ORDER_DESCENDING) {
throw new UnsupportedOrderException(‘Unsupported order encountered.’, 1242816074);
}
….

What is the reason for this check in the parseOrderings function ?

Regards
Henrik

Reviews 0

No change resolves or names this issue in its commit message, and no comment links one.

Fix this issue

Start the commit message of your patch with this. Add what the patch does, and the versions it goes to in "Releases:". How to create a patch

Commit message
[BUGFIX] Allow for Collation in extbase repository queries Resolves: #89671 Releases: main