==== Problem
The main wish-index table (with all the wishes) is failing to get rows in the requested language. If you browse to [[ https://meta.wikimedia.org/wiki/Community_Wishlist/Wishes | Community Wishlist/Wishes ]] and sort by vote count descending, you should see several rows at the bottom in Japanese. Click on them and you'll see there are English translations, yet we aren't showing them.
A simplified example production query:
```lang=sql
SELECT cr_page, crt_title, cr_base_lang, crt_lang
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
)
ORDER BY
crt_title DESC
LIMIT
22;
```
This contains a result for [[ https://meta.wikimedia.org/wiki/Community_Wishlist/W22 | W22 ]] (in English, `Make it easier for newcomers to create their first article`), but instead we only got the Japanese translation `新規利用者が最初の記事を作成しやすくなるようにしてほしい` – Japanese in this case being the base language of that wish.
What we're trying to do here is fetch all rows for the requested language along with those in its fallback chain, OR where the base language = requested language (i.e. there are no suitable translations). In this case, we are requesting English, which has no fallback languages. We want 10 rows of results, +1 more for pagination. So going by [[ https://gerrit.wikimedia.org/r/plugins/gitiles/mediawiki/extensions/CommunityRequests/+/e23135c03bee13b0b6298e710bc02237d3c39027/includes/AbstractWishlistStore.php#384 | our logic ]], the LIMIT should be 22 – that is `( 10+1 ) * 2` (the 2 being there to account for the base lang rows).
**Working theory**
This perhaps has something to do with the order of the rows in the DB. We know a lot of those FuzzyBot jobs failed during the migration, so the translations didn't get saved until a day or so later when they were re-marked for translation. Still, we should write our queries to be resilient enough to handle this scenario.
==== Possible solutions
I was originally thinking we could just do two queries. First for the requested language and its fallbacks, and the other for `crt_lang = cr_base_lang`. It's a performance hit but it'd at least mean we know for certain we'll have the translations for the requested language. We could also do our processing after the first query, and if there are enough results, we don't even need the second one. The problem with that is we might lose a reliable order when we want to sort by title.
Taking that idea a step further, maybe the first one could only be against `communtiyrequests_entities` (where all the filters are applied), so that would dictate the definitive order, so long as the title isn't part of the sorting criteria. So then the second query only looks for translations in the requested language.
Other ideas are doing some sort of subquery or other more complicated means to get it all in one query and in a consistent order. That probably should involve verifying the `EXPLAIN` results don't look terrible.