#3732 - Smarter DB sorting for multi-lang sites

Identifier #3732
Issue type Feature request or suggestion
Title Smarter DB sorting for multi-lang sites
Status Completed
Tags

Roadmap: v11 partial implementation (custom)

Type: Internationalisation (custom)

Handling member Chris Graham
Addon core_language_editing
Description Sorting 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.
Steps to reproduce

Funded? No
The system will post a comment when this issue is modified (e.g., status changes). To be notified of this, click "Enable comment notifications".

Rating

Unrated