Translate: Optimize queries, caching, and input handling in Locale route - #1038

The following accounts have interacted with this PR and/or linked issues. I will continue to update these lists as activity occurs. You can also manually ask me to refresh this list by adding the props-bot label.

Core Committers: Use this line as a base for the props when committing in SVN:

Props timse201, obenland.

To understand the WordPress project's expectations around crediting contributors, please review the Contributor Attribution page in the Core Handbook.

No actionable comments were generated in the recent review. 🎉

Pre-merge checks: 5

Check name	Status	Explanation
Docstring Coverage		Docstring coverage is 92.31% which is sufficient. The required threshold is 80.00%. Docstring coverage is scoped to functions touched by this diff. Analyzed 13 functions across 1 files.
Linked Issues check		Check skipped because no linked issues were found for this pull request.
Out of Scope Changes check		Check skipped because no linked issues were found for this pull request.
Description Check		Check skipped - CodeRabbit’s high-level summary is enabled.
Title check		The title clearly and concisely summarizes the main changes: query optimization, caching, and input handling in the Locale route.

• Autofix · Keep fixing CodeRabbit findings and required CI, and resolving merge conflicts

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

_{Comment @coderabbitai help to get the list of available commands.}

Some comparison. First is the old behavior.

get_active_sub_projects

• Runs an individual SQL query for each sub-project inside a foreach loop to find child projects.
• Fetches all nested child projects in a single batch query and organizes them in PHP.

get_active_sub_projects

• Does not cache query results in memory; repeats database queries on multiple calls.
• Saves project lists in a static $cache array keyed by project ID and arguments.

get_paged_active_sub_projects

• Uses deprecated SQL_CALC_FOUND_ROWS, forcing MySQL to perform a full table scan and expensive filesort across all matching rows to calculate pagination.
• Splits pagination into a LIMIT data query and a lightweight COUNT(*) query without ORDER BY, avoiding unnecessary filesorts and table scans.

get_translation_editors

• Runs a new database query for every parent project level inside a while loop.
• Collects all parent project IDs in memory first and fetches editors using a single query.

get_locale_contributors

• Runs translation set lookups and heavy statistical SQL queries separately in a loop for each project.
• Finds all translation set IDs in one query, then runs one combined query for all sets.

get_locale_contributors

• Calls get_user_by() individually for every contributor and editor
• Collects all user IDs and primes the WordPress cache in advance with cache_users().

get_locale_contributors

• Checks if a user is an editor using in_array() inside a loop
• Stores editor IDs as keys in an associative array and checks them using isset()

Early 404 Exit

• Prepares view variables and triggers apply_filters() before checking if the project list is empty.
• Checks if projects exist immediately and stops execution early with a 404 error if empty.

@obenland
I have fixed some edge cases and added some empty checks.
No usermeta is queried (WP_User::get_data_by() is used), except in favorites.
Collator is now used to sort editor names which is a bit slower. But it sorts special characters, too.
And a array_slice caps user favorites on special. (Sorting for waiting strings)

I can't find any errors that could occur now. Only when something changes on translate.WP.org.