views – addField() does not properly ensure table?

When I create a view with a table display, I configure the table columns to be sortable and set Dinstinct to avoid duplicates. I get the following SQL error.

General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column ‘XYZ’ which is not in SELECT list

A proposed solution from this issue on drupal.org is to add a custom filter which iterates through all the fields set as sortable and add them to the query. The query method of my custom filter plugin looks like this.

public function query() {
  // Force View to DISTINCT (because multiple matches makes duplicate
  // query results), and ensure all fields in DISTINCT exist in query to avoid
  // SQL error "ORDER BY clause is not in SELECT list": Table column click
  // to sort (and its default order) adds an additional field too late in the
  // game, so we preemptively add all these fields to the query to anticipate
  // it.
  $this->query->distinct = TRUE;

  // Fields the table sorting plugin might use which might not be in the query.
  $sortable_fields = array_keys(array_filter($this->view->getDisplay()->getOption('style')('options')('info'), function ($item) {
    return $item('sortable') ?? FALSE;
  }));

  // Get field table meta and add to query:
  foreach ($sortable_fields as $field_name) {
    $field = $this->view->field($field_name) ?? NULL;
    if ($field) {
      $this->query->addField($field->table, $field->realField);
    }
  }
}

This works when the fields/columns are stored in the base table, but when a field that comes from a joined table is used, this fails. The SQL error in this case is the following.

Column not found: 1054 Unknown column ‘politician.first_name’ in ‘field list’

When I look at the query which is shown with the error, I see that the politican table is joined but with politician_candidacies_mandates as alias. I find the whole query somehow confusing, but the base table of the view is sidejob.

SELECT COUNT(*) AS "expression" 
FROM 
(SELECT DISTINCT 
"sidejob"."id" AS "id", "politician"."first_name" AS "politician_first_name", "politician"."last_name" AS "politician_last_name", "politician"."party" AS "politician_party", "sidejob"."job_title" AS "sidejob_job_title", "sidejob"."job_title_extra" AS "sidejob_job_title_extra", "sidejob_organization"."name" AS "sidejob_organization_name", "sidejob"."category" AS "sidejob_category", "sidejob"."interval" AS "sidejob_interval", "sidejob"."income_level" AS "sidejob_income_level", "sidejob"."created" AS "sidejob_created", "sidejob"."changed" AS "sidejob_changed", "sidejob"."data_change_date" AS "sidejob_data_change_date", "candidacies_mandates_sidejob__mandates"."id" AS "candidacies_mandates_sidejob__mandates_id", "politician_candidacies_mandates"."id" AS "politician_candidacies_mandates_id", "sidejob_organization_sidejob"."id" AS "sidejob_organization_sidejob_id", 1 AS "expression" FROM {sidejob} "sidejob" 

LEFT JOIN {sidejob__mandates} "sidejob__mandates" ON sidejob.id = sidejob__mandates.entity_id AND sidejob__mandates.deleted = :views_join_condition_0 

LEFT JOIN {candidacies_mandates} "candidacies_mandates_sidejob__mandates" ON sidejob__mandates.mandates_target_id = candidacies_mandates_sidejob__mandates.id 

LEFT JOIN {politician} "politician_candidacies_mandates" ON candidacies_mandates_sidejob__mandates.politician = politician_candidacies_mandates.id 

LEFT JOIN {sidejob_organization} "sidejob_organization_sidejob" ON sidejob.sidejob_organization = sidejob_organization_sidejob.id
) "subquery"

When I dig into addField() and ensureTable(), I don’t understand how I could assure the correct relation is used. Maybe the whole solution is not a solution.

Any hint and idea is highly appreciated.