Ooh, maybe LEFT JOIN the entities table with translations, such that we get rows for all entities even if there are no translations for the requested lang. Then, we fill in the gaps with a second query for the base language translations. That should maybe work?

Change #1194364 had a related patch set uploaded (by MusikAnimal; author: MusikAnimal):

[mediawiki/extensions/CommunityRequests@master] ApiQueryWishesTest: Add test case that should pass

https://gerrit.wikimedia.org/r/1194364

I believe the above patch demonstrates the problem.

the translations didn't get saved until a day or so later when they were re-marked for translation.

Is the issue here that the translation rows aren't getting saved, or aren't being queried correctly? It sounds like the latter.

From your example above, you're saying that we're not getting the following two rows in the results?

i.e. due to being ordered by title and then limited, the English row is not being included in the results.

Anyway, should we perhaps be doing one query per language in the fallback chain, e.g. something like this?:

( SELECT cr_page, cr_base_lang, crt_lang, crt_title
    FROM `communityrequests_entities`
    JOIN `communityrequests_translations` transbase ON crt_entity = cr_page AND transbase.crt_lang = cr_base_lang
    WHERE cr_entity_type = 0 )
UNION
( SELECT cr_page, cr_base_lang, crt_lang, crt_title
    FROM `communityrequests_entities`
    JOIN `communityrequests_translations` transbase ON crt_entity = cr_page AND transbase.crt_lang = 'pt'
    WHERE cr_entity_type = 0 )
UNION
( SELECT cr_page, cr_base_lang, crt_lang, crt_title
    FROM `communityrequests_entities`
    JOIN `communityrequests_translations` transbase ON crt_entity = cr_page AND transbase.crt_lang = 'pt-br'
    WHERE cr_entity_type = 0 )

ORDER BY crt_title desc
LIMIT 22;

Indeed, the data is there, it's just our query that's broken. A UNION'd query like that might work, yes! It didn't work in my testing. Running it on production, I got the same Japanese translation for W22 instead of the English one.

I've got a few other ideas, but I'm not claiming this task until I find something that works. Anyone else should feel free to grab it if they've got a solution in mind.

I'm thinking this might work:

SELECT cr_page, cr_base_lang, crt_lang, crt_title
FROM `communityrequests_entities`
JOIN `communityrequests_translations` ON crt_entity = cr_page
WHERE cr_entity_type = 0
  AND (
    crt_lang = 'en'
    OR (
      crt_lang = cr_base_lang
      AND NOT EXISTS (
        SELECT 1
        FROM `communityrequests_translations` transinner
        WHERE transinner.crt_lang = 'en'
          AND transinner.crt_entity = cr_page
      )
    )
  )
ORDER BY crt_title DESC
LIMIT 22;

It could probably be simplified, but initial tests look promising. Definitively, any wishes we get back that are not already in pt or pt-br do not exist in pt or pt-br. I find that reassuring.

EXPLAIN results:

stdClass Object
(
    [id] => 1
    [select_type] => PRIMARY
    [table] => communityrequests_translations
    [type] => index
    [possible_keys] => PRIMARY,crt_lang_title
    [key] => crt_lang_title
    [key_len] => 294
    [ref] => 
    [rows] => 1164
    [Extra] => Using where; Using index; Using filesort
)
stdClass Object
(
    [id] => 1
    [select_type] => PRIMARY
    [table] => communityrequests_entities
    [type] => eq_ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 4
    [ref] => metawiki.communityrequests_translations.crt_entity
    [rows] => 1
    [Extra] => Using where
)
stdClass Object
(
    [id] => 2
    [select_type] => MATERIALIZED
    [table] => transinner
    [type] => ref
    [possible_keys] => PRIMARY,crt_lang_title
    [key] => PRIMARY
    [key_len] => 37
    [ref] => const
    [rows] => 403
    [Extra] => Using where; Using index
)

versus the EXPLAIN of the original query in the task description:

stdClass Object
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => communityrequests_translations
    [type] => index
    [possible_keys] => PRIMARY,crt_lang_title
    [key] => crt_lang_title
    [key_len] => 294
    [ref] => 
    [rows] => 1164
    [Extra] => Using index; Using filesort
)
stdClass Object
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => communityrequests_entities
    [type] => eq_ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 4
    [ref] => metawiki.communityrequests_translations.crt_entity
    [rows] => 1
    [Extra] => Using where
)

So it looks like it wants to use the where condition before the crt_lang_title index, which is maybe not good. However we still scan the same number of rows in the first query plan. The new query also has the MATERIALIZED query plan which scans an additional 403 rows. That number will be smaller for languages with fewer translations. I.e. for 'pt', 'pt-br', 'en' it is only 37 rows.

Either way, it will be quite a while before these queries are ever slow, so maybe if nothing else this is acceptable as a stopgap measure?

What do we think?

I'm not 100% understanding how that will fetch the rows for intermediate fallback languages. But go for it!

I'm not 100% understanding how that will fetch the rows for intermediate fallback languages. But go for it!

It would be the same as above but instead of crt_lang = 'en' it'd be i.e. crt_lang IN ('pt', 'pt-br', 'en'), and with the LIMIT adjusted. We get the list of language codes before running the query.

Change #1194364 merged by jenkins-bot:

[mediawiki/extensions/CommunityRequests@master] AbstractWishlistStore: revise main query to ensure we get translations

https://gerrit.wikimedia.org/r/1194364

@MusikAnimal As discussed we will be closing this task since it will be fixed in T408083: RenderTranslationJobs sometimes fail and translations are not saved and T404770: Wish index page incorrectly assumes translation subpage exists. Thanks!