Viewsクエリの条件式にサブクエリを追加する方法

modules/custom/your_module/your_module.module

/**
 * @param \Drupal\views\ViewExecutable $view
 * @param \Drupal\views\Plugin\views\query\QueryPluginBase $query
 */
function your_module_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view->id() == 'your_view_id') {
    $query->addWhereExpression(
      $whereGroup,
      'node__field_xxxxx_value = (' .
      'SELECT MAX(c.field_yyyyy_value) FROM node__field_yyyyy c ' .
      'INNER JOIN node__field_zzzzz i ON i.entity_id = c.entity_id AND i.deleted = 0 ' .
      'WHERE c.deleted = 0 ' .
      'AND i.field_zzzzz_value = node__field_zzzzz.field_zzzzz_value' .
      ')');
  }
}