How to use sql MAX() function in views plugin query

Question

I am creating a custom views filter for a custom views field. The following SQL will accomplish the filter I seek:

select nid, moderation_state, content_entity_id, MAX(content_entity_revision_id) 
  from node inner join content_moderation_state_field_revision on nid = content_entity_id 
    where content_entity_id = 59 group by content_entity_id

In my views filter plugin php file, I have the following code to add the above sql to the filter query:

/**
   * Helper function that builds the query.
   */
  public function query() {
    if (!empty($this->value)) {
      $configuration = [
        'table' => 'content_moderation_state_field_revision',
        'field' => 'content_entity_id',
        'left_table' => 'node_field_data',
        'left_field' => 'nid',
        'operator' => '=',
      ];
      $join = Views::pluginManager('join')->createInstance('standard', $configuration);
      $this->query->addRelationship('content_moderation_state_field_revision', $join, 'node_field_data');
      $this->query->addExpression('MAX(content_moderation_state_field_revision.content_entity_revision_id)');
      $this->query->groupBy("content_moderation_state_field_revision.content_entity_id");
      $this->query->addWhere('AND', 'content_moderation_state_field_revision', $this->value, 'IN');
    }
  }

This error is generated when a query is executed:

Error: Call to undefined method Drupal\views\Plugin\views\query\Sql::addExpression()

While addExpression() is part of core database api it is not supported in the views query plugin sql api,

So, I need a workaround to add the MAX() expression to the query. Any suggestions on how to do this?

Answer 1

I found a workaround!

This SQL code essentially gets me what I am looking for:

select * from content_moderation_state_field_revision where (content_entity_id, content_entity_revision_id) in 
  ( 
    select content_entity_id, MAX(content_entity_revision_id) from content_moderation_state_field_revision group by content_entity_id 
  ) 
  and moderation_state = 'draft'

Note that the MAX and group by functions are all in the SQL subquery, which doesn't require any $row input. So, I created a function in my module to output the results of that subquery, and added it as a condition in the plugin filter query. Like this:

/**
   * Helper function that builds the query.
   */
  public function query() {
    if (!empty($this->value)) {
      // Views join handler plugin: https://api.drupal.org/api/drupal/core%21modules%21views%21src%21Plugin%21views%21join%21JoinPluginBase.php/group/views_join_handlers/8.2.x
      $configuration = [
        'type' => 'INNER',
        'table' => 'content_moderation_state_field_revision',
        'field' => 'content_entity_id',
        'left_table' => 'node_field_data',
        'left_field' => 'nid',
        'operator' => '=',
      ];
      $join = Views::pluginManager('join')->createInstance('standard', $configuration);
      $this->query->addRelationship('content_moderation_state_field_revision', $join, 'node_field_data');     
      $this->query->addGroupBy("content_moderation_state_field_revision.content_entity_id");
      $revision_ids = sbn_moderation_state_sql_subquery(); // returns highest_entity_revision_id
      $this->query->addWhere('AND', 'content_entity_revision_id', $revision_ids, 'IN');
      $this->query->addWhere('AND', 'moderation_state', $this->value, 'IN');
    }
  }

I still have the addGroupBy clause, but I don't think it's necessary because the MAX function is in the subquery which takes care of returning the highest content_entity_revision_id for each node.

Of course, I still don't know the correct syntax for adding MAX() function in views plugin query sql api syntax. If anyone knows, please add that answer as well.

Anyway, the filter works like a charm -- I can filter the view by published or draft nodes or both.