Sort by name not working correctly in multilingual project

[chladog]

Is your feature request related to a problem? Please describe.
List query builder sort's feature on translated properties like "name" of ProductVariant or Product doesn't work correctly, as the query is sorted based on all values, not of the current languageCode from ctx object.
E.g. I have list of products (be the number "ID") in EN:

1. Arg
2. Blaigh
3. Dooh

and their translation in DE:

1. Bimbim
2. Kiokio
3. Aant

the result for EN site will wrongly be "Dooh, Arg, Blaigh", and what's worse consequently skip and take stop working it will just return fewer results randomly.

Describe the solution you'd like
In my case I joined translation of wanted language on different alias and sort on it, therefore in core I believe switching translation eager settings to false and in query builder leftjoinselect translation with condition based on current languageCode
qb.leftJoinAndSelect(qb.alias + '.translations', 'translation', 'translation."languageCode" = :langCode', {langCode: ctx.languageCode})
will fix the issue and we will get better performance as a bonus, but mind the need of providing defaults for those that have missing translation

[chladog]

This should be tagged as bug.

[michaelbromley]

Good catch.

I want to keep translations as eager - they are always needed and using eager loading quite significantly simplifies a bunch of code.

Anyway, I think I managed to solve the basic issues by adding the following to the ListQueryBuilder.build() method:

const { columns, translationColumns, alias } = getColumnMetadata(rawConnection, entity);
if (translationColumns.length) {
    const languageCode = extendedOptions.ctx?.languageCode || DEFAULT_LANGUAGE_CODE;
    const translationsAlias = qb.connection.namingStrategy.eagerJoinRelationAlias(
        alias,
        'translations',
    );
    qb.andWhere(`${translationsAlias}.languageCode = :languageCode`, { languageCode });
}

However, this does not handle the case in which a translation is missing in a given language. In that case, the item with the missing language is omitted from the result set. I'm having trouble coming up with an SQL query which can handle that, the logic being:

# pseudo-code

SELECT entity 
LEFT JOIN translations on translations.baseId = entity.id
WHERE translations.languageCode = :languageCode
but include _all_ entities even if no translation exists

That is beyond my SQL-fu right now.

[michaelbromley]

Getting closer, with a query like:

SELECT "label", "languageCode", "name"
FROM "test_entity" "testentity"
LEFT JOIN
(SELECT * FROM "test_entity_translation" "testentity_translations" 
  WHERE "testentity_translations"."languageCode" = 'de') t1 
ON "t1"."baseId"="testentity"."id"

This gives a row for each of the entity, and null values if the translation doesn't exist. Now trying to figure out how to build such a query in an automated way via TypeORM...

Update: Unfortunately it seems like there is no way with the existing TypeORM API to join a custom subquery as a relation, see typeorm/typeorm#2074.

[michaelbromley]

Finally figured out a way to solve that last aspect of the problem. If the current language is not the default, and a translation doesn't exist in that langauge, then it will fall back to the default language. The SQL used in this case looks like (assuming a default LanguageCode of 'en'):

WHERE
  "testentity_translations"."languageCode" = 'de'
  OR
  EXISTS (SELECT * FROM "test_entity_translation" WHERE "test_entity_translation"."baseId" = "testentity"."id"  AND "test_entity_translation"."languageCode" = 'en')
  AND NOT EXISTS (SELECT * FROM "test_entity_translation" WHERE "test_entity_translation"."baseId" = "testentity"."id"  AND "test_entity_translation"."languageCode" = 'de')