Special sorting requirement in views for a multi-value field: how to avoid duplicated result items

This is another attempt to solve Custom views filter plugin: filter over two fields of a custom entity where one of the fields is a multi value field in a seperate table

I have create a basefield (committee_role) for a custom entity (committee_membership). The entity holds data about the membership of a deputy in a committee in parliament. Politicians can have more than one role in a committee but at the same time we want to sort the committee memberships depending on the hierarchy of the role, so that first comes the chair person, then the vice chair person, the spokesperson, the secretary and then all the other members (for example).

So far the role field was a one value field and we used a custom views sort plugin to achieve the sorting by hierarchie. The hierarchy is defined in the $allowed_values array which is set in allowed values function.

class SortCommitteeMembership extends SortPluginBase {
  public function query(): void {
    $this->ensureMyTable();
    $field_storage = \Drupal::service('entity_field.manager')->getFieldStorageDefinitions('committee_membership')['committee_role'];
    $allowed_values = array_keys(options_allowed_values($field_storage));
    $formula = 'FIELD(' . $this->tableAlias . '.' . $this->field . ', ' . implode(', ', array_map(array(Database::getConnection(), 'quote'), $allowed_values)) . ')';

    $this->query->addOrderBy(NULL, $formula, $this->options['order'], $this->tableAlias . '_' . $this->field . '_keys');
  }
}

The result for $formula is something like

FIELD(committee_membership__committee_role.committee_role_value, 'chairperson', 'vice_chairperson', 'foreperson', 'spokesperson', 'alternate_spokesperson', 'secretary', 'alternate_secretary', 'advisory_member', 'eligible_member', 'member', 'alternate_member') AS "committee_membership__committee_role_committee_role_value_ke"

To be honest: I am not so sure what this does :) it just worked fine as long the committee_role only had one value.

Now I have changed the cardinality of the role field to unlimited, the $formula above already shows that the values for the committee_role are now in a seperate table (committee_membership__committee_role). And when I now use this kind of sorting it still works (the committee memberships are hierarchily ordered) but I get multiple result items for politicians with multiple roles no matter of "Distinct" and aggregation in the views configuration.

My idea to avoid the duplicated results: the query should only take one role into account for sorting and this should be the one with the highest hierarchy level of the roles set for the membership. Is there a way to achieve that? Something like this would be needed:

• Get the one role related to a committee membership which is the highest in the hierarchy level
• Then sort all committee memberships by the one role found by the hierarchy levels