Sort nodes by weight of taxonomy term attached to field

Question

In a query for nodes I'm trying to sort the nodes by the weight of a taxonomy term attached to one of the node fields.

Here is how my query is built

    $node_storage = \Drupal::entityTypeManager()
      ->getStorage('node');
    $my_query = $node_storage
      ->getQuery()
      ->accessCheck(FALSE)
      ->condition('type', 'my_content_type')
      ->condition('status', NodeInterface::PUBLISHED)
      ->condition('field_my_custom_field', $this->id());

It's the sorting part where I get stuck. Here's my attempt

    $my_query->leftJoin('taxonomy_term_field_data', 'td', 'td.tid = node_field_data.field_my_custom_field.target_id')
    ->orderBy('td.weight', 'ASC')
    ->execute();

By leftJoin is not available. I've looked at https://api.drupal.org/api/drupal/core%21lib%21Drupal%21Core%21Entity%21Query%21Sql%21Query.php/class/Query/9.0.x and I do see that sqlQuery is a property on the Query object that you can perform leftJoin on it but sqlQuery is a protected property.

Is sorting by this possible?

Answer 1

I was able to sort by the taxonomy weight with this

->sort('field_my_custom_field.entity.weight','DESC');