View Issue Details

IDProjectCategoryView StatusLast Update
0003732Composrcore_language_editingpublic2019-06-27 19:02
ReporterChris GrahamAssigned To 
Severityfeature 
Status non-assignedResolutionopen 
Product Version 
Fixed in Version 
Summary0003732: Smarter DB sorting for multi-lang sites
DescriptionSorting order is undefined for sites with partial content translations.

Imagine a site with these entries...
1) German=Foo,English=Foo
2) German=Hallo,English=Hello
3) German=Zehr,English=Very
4) German=(untranslated),English=Something
5) German=Uber,English=(untranslated)

Viewing in German you'd expect to get this sort order:
1) Foo
2) Hallo
3) Something
4) Uber
5) Zehr

But you'd get:
1) Something
2) Foo
3) Hallo
4) Uber
5) Zehr

Because 'Something' would actually be sorting as '' (due to missing translation).

This is because an SQL JOIN is used to join in the translation on the correct language, and then the sorting is done on that translation. No translation, no sort order - regardless of whether there is a fallback language it could sort by.

SELECT * FROM somewhere r JOIN translate t ON t.id=r.some_string AND t.lang=DE

We have to sort inside the database (with indexing) for performance reasons. Sorting in-memory means reading all the content out, even if there are a million entries.

Instead of a naive join we could do something like:

SELECT * FROM somewhere r JOIN (SELECT * FROM translate t WHERE t.id=r.some_string AND (t.lang=DE OR t.lang=EN) ORDER BY t.lang=DE DESC LIMIT 1)

This may not work on all database backends, but I think it will work on MySQL.

We'd need to create some kind of abstraction to handle it. Maybe different database drivers could output different SQL.
TagsRoadmap: v11, Type: Internationalisation
Attach Tags
Time estimation (hours)8
Sponsorship open

Activities

Guest

2018-11-06 22:33

viewer   ~0005874

A workaround is with the new hidden force_memory_sort__<catalogue-name> option.

Issue History

Date Modified Username Field Change
2018-11-06 03:35 Chris Graham New Issue
2018-11-06 13:36 Chris Graham Description Updated View Revisions
2018-11-06 15:11 Chris Graham Category core => core_language_editing
2018-11-06 22:33 Guest Note Added: 0005874
2018-11-08 22:36 Chris Graham Tag Attached: ocProducts client-work (likely)
2018-11-10 21:08 Chris Graham Tag Attached: Type: Internationalisation
2018-11-10 21:12 Chris Graham Tag Detached: ocProducts client-work (likely)
2019-06-27 19:02 Chris Graham Tag Attached: Roadmap: v11