Bug #89671

Allow for Collation in extbase repository queries

Added by Henrik Jensen 2 months ago. Updated 11 days ago.

Status:
Accepted
Priority:
Should have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2019-11-13
Due date:
% Done:

0%

TYPO3 Version:
8
PHP Version:
Tags:
database extbase sortorder
Complexity:
Is Regression:
Sprint Focus:

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

History

#1 Updated by Alexander Schnitzler 12 days ago

  • Status changed from New to Accepted

Hi Henrik,
I understand the problem although I am in the lucky position as a German that our special characters are properly sorted.

Personally, I think this is an issue which should be dealt with core-wide. You want to have the same sorting everywhere, no matter if you fetch data with Extbase, typoscript or whatever.

As you asked for why the code is implemented as is:
The QOM is a PHP implementation of an old, unknown version of the original Java QOM (Query Object Model).
Currently I don't know if Java supported the use of custom orderings back then. Neither do I know if they support it now.
However, that's the history regarding the current state.

I am very aware that the persistence of Extbase is a PITA and I can't promise any changes anytime soon.

I have this in mind and I mark this as accepted but I am honest that I don't know if this will ever be tackled.

#2 Updated by Benni Mack 11 days ago

Hey everybody,

claus harup approached me recently with the same problem. We figured out that you'd need to set the collation on the DB query like this:

SELECT uid, title FROM backend_layout ORDER BY title COLLATE 'utf8_danish_ci';

This would mean that we'd need to add this collation on every SQL query, and we'd need to define the collation on a per-language-level.

Also available in: Atom PDF