JOIN subquery as right table in Views

Question

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->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' => $subquery,
      'field' => 'entity_id',
      'left_table' => 'node_field_data',
      'left_field' => 'nid',
    ];

    $join = Drupal::service('plugin.manager.views.join')->createInstance('standard', $definition);
    $query->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...

Answer 1

It worked (in views plugin).

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

// create join
$join_definition = [
      'table formula' => $sub_query,
      'field' => 'entity_id',
      'left_table' => 'node_field_data',
      'left_field' => 'nid',
      'adjust' => TRUE,
    ];
$join = $this->joinManager->createInstance('standard', $join_definition);
$this->query->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')->createInstance('standard', $join_definition);
$query->addRelationship('event_date', $join, 'node_field_data');