JOIN subquery as right table in Views I have a node field named event_date which contains multiple values of dates. 

The table for the field looks like this:

node__field_event_date
+----------------+------------+
| entity_id(nid) | event_date |
+----------------+------------+
|              1 | 2019-1-1   |
|              1 | 2019-1-2   |
|              2 | 2019-1-3   |
|              2 | 2019-1-4   |
+----------------+------------+


I would like to join only earliest event_date for each node to the node base table(node_field_data).

node_field_data(short version)
+-----+--------+
| nid | title  |
+-----+--------+
|   1 | event1 |
|   2 | event2 |
+-----+--------+


The ideal result is: 

+-----+--------+----------------+-------------------+
| nid | title  | entity_id(nid) | EarliestEventDate |
+-----+--------+----------------+-------------------+
|   1 | event1 |              1 | 2019-1-1          |
|   2 | event2 |              2 | 2019-1-3          |
+-----+--------+----------------+-------------------+


In raw sql, it can be written like this:

SELECT * FROM `node_field_data`
INNER JOIN (
  SELECT entity_id, MIN(field_event_date) AS EarliestEventDate
  FROM node__field_event_date
  GROUP BY entity_id
) AS event_date
ON node_field_data.nid = event_date.entity_id


But the problem is, the join should be done as a part of query for a View.

I tried the code below, but the Views didn't parse the subquery at all.

// in MY_MODULE.module

/**
 * Implements hook_views_query_alter().
 */
function MY_MODULE_views_query_alter(ViewExecutable $view, QueryPluginBase $query) {
  if ($view-&gt;id() === 'events') {

    $subquery = "(SELECT entity_id, MIN(field_event_date_value) AS EarliestEventDate FROM node__field_event_date GROUP BY entity_id) AS node__field_event_date";

    $definition = [
      'table' =&gt; $subquery,
      'field' =&gt; 'entity_id',
      'left_table' =&gt; 'node_field_data',
      'left_field' =&gt; 'nid',
    ];

    $join = Drupal::service('plugin.manager.views.join')-&gt;createInstance('standard', $definition);
    $query-&gt;addRelationship('event_date', $join, 'node_field_data');
  }
}


What appeared on SQL preview in the view editing page:

LEFT JOIN {SELECTentity_idMINfield_event_date_valueASEarliestEventDateFROMnode__field_event_dateGROUPBYentity_idASnode__field_event_date} event_date ON node_field_data.nid = event_date.entity_id


Maybe I should try Views Plugin? But I don't have much experience making it and have no idea plugins can accept subquery like this. 

If anyone have thoughts or ideas please give me advice...
It worked (in views plugin).

// create subquery using database api
$sub_query = \Drupal::database()-&gt;select('node__field_event_date', 'n');
    $sub_query-&gt;addField('n', 'entity_id');
    $sub_query-&gt;addExpression("MIN(n.field_event_date_value)", 'EarliestEventDate');
    $sub_query-&gt;groupBy("n.entity_id");

// create join
$join_definition = [
      'table formula' =&gt; $sub_query,
      'field' =&gt; 'entity_id',
      'left_table' =&gt; 'node_field_data',
      'left_field' =&gt; 'nid',
      'adjust' =&gt; TRUE,
    ];
$join = $this-&gt;joinManager-&gt;createInstance('standard', $join_definition);
$this-&gt;query-&gt;addRelationship('event_date', $join, 'node_field_data');


Edit:
If you do the same thing in hook_query_alter, change the last two lines like this.

$join = Drupal::service('plugin.manager.views.join')-&gt;createInstance('standard', $join_definition);
$query-&gt;addRelationship('event_date', $join, 'node_field_data');