Enabling aggregation breaks views with "Moderation state" fields

Hello,

Facing the same issue. Is there any associated drupal issue link for this issue?

Thanks!
Adi

This still happens in 9.0.x but the workaround no longer applies as the relationship has been removed by the looks of it.

This fixes the issue for me, simply by switching off aggregation and forcing it as FALSE during ::query.

I can't think of a specific use case, but would it be valid to do aggregation on the moderation state field, through the join to the content_moderation_state entity? Ideally the computed field would behave as close to a normal field as possible, which would mean supporting this, but that doesn't look very easy. Maybe opened as a follow-up feature request?

I confirm that we also ran into this problem. The patch fixes the problem. The patch applies cleanly to Drupal 8.8.8.

The view where we ran into this problem shows the editor all the nodes they have submitted moderation state changes on, regardless of if they are the Revision user or not.

The scenario is like this:

• The editor can make a moderation state change of Request publish but cannot publish the node.
• The publisher publishes the node by changing its moderation state to Published.
• The editor can now see that the node's moderation state has been changed to Published.

The views query requires GROUP BY because otherwise the results of the views query will include all the node revisions from that user, thus duplicates. Thus the need for aggregation in the view.

This looked promising but with my use case the issue behind this is actually bigger. Applying the patch from #6 does not allow sorting by moderation states when aggregation is on, as forcing GROUP BY off on the moderation state makes the field unusable in ORDER BY.

While the exception is thrown when the grouping on moderation state is set to "value", keeping grouping at "entity id" makes the view work just without sorting by moderation state available. Obviously this can lead to unwanted results in some cases where revisions would matter.

I'm not quite sure where to start looking for a proper solution, as the ensureMyTable method to me looks like it does the join as I would expect, but the clickSort method seems not to make use of it.

Marking as Needs work as I kind of would like to push for a more elegant solution as from my perspective this is now broken with the old relationship removed in 9.0.

I had a simple workaround pointed out by a colleague. We added a hook_query_TAG_alter() that contains the following:

  $order = &$query->getOrderBy();
  foreach ($order as $key => $val) {
    $query->groupBy($key);
  }

This effectively adds the content moderation state field to GROUP BY when it is in ORDER BY, giving MySQL what it wanted. Does not by the way work if DISTINCT is set.

I don't think having to do that kind of alteration is really the correct way to make this work, but maybe this could point for a better solution?

It seems it is still an issue in latest Drupal version, bumping to 9.3.x-dev.

I think I found a better solution. I have tested it works with aggregation and shorting as well.

The patch in #14 does not work if the view shows content types with and without workflow enabled. The following error is thrown:
Fatal error: Uncaught Error: Call to a member function getTypePlugin() on null in .../web/core/modules/content_moderation/src/Plugin/Field/FieldFormatter/ContentModerationStateFormatter.php:64

Rerolled the patch of #14 to fix this issue. Patched against 9.5.x.

I have fix cs error. Please review it

This issue is being reviewed by the kind folks in Slack, #need-reveiw-queue. We are working to keep the size of Needs Review queue [2700+ issues] to around 400 (1 month or less), following Review a patch or merge require as a guide.

Tested this on 10.1.x but I receive

Error: Class "Peast\Peast" not found in Drupal\Core\Asset\JsOptimizer->optimize() (line 37 of /var/www/html/web/core/lib/Drupal/Core/Asset/JsOptimizer.php).

In the logs and the patch in #17 does not fix the issue I'm seeing.
Tagging for tests and an issue summary update.

Thanks

#17 applied to 10.1.3 and allowed my view with aggregation to work with a Moderation State field.

Setting to Needs Review as there are no steps to reproduce the error described in #18 and this is working for me.

Issue summary update and tests will still be needed.

I have just tested with 10.2.2 and the #17 patch seems to be working.

Added patch 17 for Drupal 10.2.4. It only fixes the issue if the aggregation setting for the moderation state is set to aggregate on the Entity ID instead of the Value. (See attached screenshot for more information.)

Thank you for this. #17 worked for me in Drupal 10.2.3.