How to query on most recent language specific revision? Entity query latestRevision() is not language aware

Here's some code that seems to do what my use-case requires, in Drupal 10, running on PostgreSQL, based on my understanding of how the node translation system works (which might be wrong)...

  /**
   * Load the latest revision for a translation.
   *
   * @param int|string $nodeId
   *   The node ID to load.
   * @param string $langcode
   *   The language code to load.
   *
   * @return \Drupal\node\NodeInterface|null
   *   The node revision for the given node in the given language.
   */
  public function loadLatestRevisionTranslation(int|string $nodeId, string $langcode): ?NodeInterface {
    $answer = NULL;

    // Select rows from the node_field_revision table, which contains all
    // revisions from all languages (node_revision only contains revisions for
    // the language that the node was created in).
    $query = $this->database->select('node_field_revision', 'nfr');

    // Limit the selection to rows that match the given node and language code.
    $query->condition('nfr.nid', $nodeId);
    $query->condition('nfr.langcode', $langcode);

    // When you save a revision for a node, revision_translation_affected is set
    // to 1 for the language that you were editing when you clicked "Save". If
    // the node happens to have other translations, all translations get a new
    // revision with the same vid. However, in this case,
    // revision_translation_affected is set to 1 for the language you were
    // editing when you clicked "Save" as before; but the revisions for the
    // other languages get set to NULL.
    // We only care about the revision that the user clicked "Save" on, so we
    // limit the selection to rows where revision_translation_affected is set to
    // 1.
    $query->condition('nfr.revision_translation_affected', 1);

    // Tell the query engine to group rows where the nid and langcode match,
    // i.e.: group rows by what we think of as a node-translation. Inside that
    // grouping of rows (a.k.a. window, sliding window, subgroup, etc.), find
    // the maximum revision ID (vid). Because vids are assigned sequentially,
    // this effectively finds the most-recent revision for that translation.
    //
    // Note that if this is the ONLY field that we are selecting, we could just
    // SELECT MAX(vid) here, without the sliding window... however, if we
    // decided to SELECT any other fields (i.e.: other use-cases, future
    // changes, debugging purposes, etc.), we would get different results
    // (unless we are very careful about how we prepare the GROUP BY and ORDER
    // BY clauses). Using a window function allows us to SELECT other fields
    // without affecting the result in this column.
    $query->addExpression('MAX(nfr.vid) OVER (PARTITION BY nfr.nid, nfr.langcode)');

    // Limits/ranges are applied at the end: and in this case, we would have one
    // row for each revision created for the given translation, with the same
    // MAX(vid) value in each row, so we only need to return the first row.
    $query->range(0, 1);

    // Now execute the query, get the first value in the first column, try to
    // load the node with that revision, and if we are successful, assign the
    // variable that we return.
    try {
      $statement = $query->execute();
      $result = $statement->fetchField(0);
      if (\is_numeric($result)) {
        $candidate = $this->getNodeStorage()->loadRevision($result);
        if ($candidate instanceof NodeInterface) {
          $answer = $candidate->getTranslation($langcode);
        }
      }
    }
    catch (\Throwable $t) {
      // No-op: if an exception occurs, return NULL.
    }

    return $answer;
  }