0

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...

  • Don't use a subquery, use SQL's having – Hudri Nov 19 '19 at 7:00
2

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');
|improve this answer|||||
  • it would be helpful if you add a little more information on how it worked. You refer to $this->joinManager but there is no $this in the hook_views_query_alter context from your question so i'm having trouble following how this answer solves the initial problem. – Francis Yaconiello Feb 23 at 23:06
  • @FrancisYaconiello Sorry, I forgot to write I did it in views Plugin. If you do this in hook, you need to instantiate join object at first. – 863 Feb 25 at 2:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.