View Issue Details

IDProjectCategoryView StatusLast Update
0003732Composrcore_language_editingpublic2020-02-27 21:41
ReporterChris GrahamAssigned ToChris Graham 
Status resolvedResolutionfixed 
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 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 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 partial implementation, Type: Internationalisation
Attach Tags
Time estimation (hours)8
Sponsorship open


related to 0003797 resolvedChris Graham Cleanup queries so can_arbitrary_groupby/remove_duplicate_rows 



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
2019-11-26 22:03 Chris Graham Relationship added related to 0003797
2020-02-09 21:40 Chris Graham Tag Detached: Roadmap: v11
2020-02-12 03:07 Chris Graham Tag Attached: Roadmap: v11 partial implementation
2020-02-27 21:41 Chris Graham Assigned To => Chris Graham
2020-02-27 21:41 Chris Graham Status non-assigned => resolved
2020-02-27 21:41 Chris Graham Resolution open => fixed