View Issue Details

Jump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
3732Composrcore_language_editingpublic2018-11-06 03:352020-02-27 21:41
ReporterChris Graham Assigned ToChris Graham  
PrioritynormalSeverityfeature 
Status resolvedResolutionfixed 
Summary3732: 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 partial implementation, Type: Internationalisation
Attach Tags
Time estimation (hours)8
Sponsorship open

Sponsor

Date Added Member Amount Sponsored

Relationships

Search Commits
related to 3797 ResolvedChris Graham Cleanup queries so can_arbitrary_groupby/remove_duplicate_rows 

Activities

Guest

2018-11-06 22:33

reporter   ~5874

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
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 3797
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 Not Assigned => Resolved
2020-02-27 21:41 Chris Graham Resolution open => fixed