Add views relationship from content to latest revision

I do not understand what you're asking for. Can you clarify?

Thanks, now I follow. I'm not sure how complex this would be. We were actually just discussing the Views integration internally, and what default views if any we want to have. It seems potentially useful, although I'm not sure if it will make it into 1.0.

One complicating factor here is that if you have a view that depends on the revision_tracker table, you inherently exclude any entity that hasn't been edited since it was moderated. That is, if you moderate only some of your entities and then use that relationship in the /admin/content view, you exclude unmoderated entities or those that have not been saved since their bundle became moderated. Hm, unless we have a LEFT JOIN in there... Hm.

OK, I've spent some time looking into this. Conclusions:

1) Technically, what we want here is a relation from node to "latest revision", which is probably somewhat similar to the "latest comment" relation.

2) By making the relation optional through the UI, that Views use a LEFT JOIN and thus avoid the problem I noted of excluding content that has no moderation record yet.

3) I cannot for the life of me figure out how to even get a relationship plugin registered. I've not been able to find any documentation on it, and the examples in core I've been trying to follow haven't led me to even a registered plugin. :-(

So yes, I'm pretty sure this can be done, but it needs someone with more Views-fu to figure out how to do so. I think it would be a double-join (from base table to entity_revision_tracker as a LEFT JOIN, and then an INNER JOIN to the revision table), but I just don't know how to make Views do that.

If someone else who can figure it out wants to offer a patch, I'm happy to review it.

I think it would be a double-join

This is true if we only want to depend on views data and not on revision_tracker table but the way I'd do it to add a relationship plugin which will update the standard revision relationship query in such a that the inner query will return the max 'revision_id'.

I think @dawehner or @damiankloip can make it clearer.

If you want to look at a complex relationship plugin then you can have a look at NodeTermData in core.

Something like this maybe.

SELECT n.nid, r.title FROM `node` n
INNER JOIN `node_field_revision`r
ON n.nid = r.nid
WHERE r.vid IN
(
  SELECT MAX(vid)
  FROM `node_field_revision`
)

A subselect in a WHERE clause is crazy slow, because it re-runs for every potential row in the result set. That's why EcliseGc and I decided against that in the first place for #2674520: Add current revision filter to views and went with the generated tracker table instead. Joining against that should be quite cheap, since the join itself acts as the filter.

Here is an initial version, which works fine in theory, but sadly there is a core bug in \Drupal\views\Plugin\views\query\Sql::loadEntities.
This method currently doesn't allow to load entities and revision of these entities at the same time.

1. +++ b/src/ViewsData.php
   @@ -0,0 +1,183 @@
   +    foreach ($this->entityTypeManager->getDefinitions() as $entity_type_id => $entity_type) {
   ...
   +              'field' => 'entity_type',
   

   Why not use only revisionable entities or moderation enabled entities?

2. +++ b/src/ViewsData.php
   @@ -0,0 +1,183 @@
   +      // By checking for the views_data handler we can exclude config entities
   +      // by default.
   

   Nice check.

3. +++ b/src/ViewsData.php
   @@ -0,0 +1,183 @@
   +        // Some entity types might not be translatable.
   

   Some content entities are not translatable?

+++ b/src/ViewsData.php
@@ -0,0 +1,183 @@
+    // Add a join for each entity type to the workbench_revision_tracker table.
+    foreach ($this->entityTypeManager->getDefinitions() as $entity_type_id => $entity_type) {

This should be using the pre-filtered method on ModerationInformation to filter down to just those types that WBM knows/cares about. Unless the goal is to apply to even non-moderated types?

Applying it to non-moderated types would make no sense because {workbench_revision_tracker} doesn't store anything related to non-moderated types.

Here is the core issue: #2714989: Views which load the same entity type as entity and non default revision cause fatal in Sql::getCacheTags()
With that the test passes, will address the feedback immediately.

Thanks a ton for these super quick reviews!

Some content entities are not translatable?

Yes, for example File, but in general we cannot really assume its there.

This should be using the pre-filtered method on ModerationInformation to filter down to just those types that WBM knows/cares about. Unless the goal is to apply to even non-moderated types?

Not sure whether it makes sense, I mean it depends whether we track revision information as well, or not. Is selectRevisionableEntities the right method to use?

1. +++ b/src/ViewsData.php
   @@ -0,0 +1,192 @@
   +      'title' => $this->t('Entity tpe'),
   

   Typo

2. +++ b/src/ViewsData.php
   @@ -0,0 +1,192 @@
   +      'title' => $this->t('Entity ID'),
   

   The title should be "Entity language"

Other this, how can I extract the moderation_state of the latest revision? I've tried with something like that:

$data['node_field_revision']['moderation_state'] = [
      'title' => $this->t('Latest revision status'),
      'field' => [
        'id' => 'standard',
      ],
      'filter' => [
        'id' => 'string',
      ],
      'argument' => [
        'id' => 'string',
      ],
      'sort' => [
        'id' => 'standard',
      ],
    ];

but the field doesn't appears in the list

@lussoluca
I think you and myself got bitten by the following core issue: #2621504: \Drupal\views\Plugin\views\field\Field::getValue needs to support entity reference items
This pat

Is this issue postpone on #2714989: Views which load the same entity type as entity and non default revision cause fatal in Sql::getCacheTags()?

@jibran
I'm seriously confused because locally I need that patch to let the test not fail.

I think we are ready here now.

RTBC also for me, this patch + #2621504: \Drupal\views\Plugin\views\field\Field::getValue needs to support entity reference items + #2714989: Views which load the same entity type as entity and non default revision cause fatal in Sql::getCacheTags() did the job

I've applied both patches but don't seem to be able to get this working.

Looks like the workbench_revision_tracker table isn't created until content is created. I get this error when trying to use the relationship when no content has been created which has a workflow attached.

Drupal\Core\Database\DatabaseExceptionWrapper: Exception in Content[content]: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'drupal.workbench_revision_tracker' doesn't exist: SELECT COUNT(*) AS expression FROM (SELECT 1 AS expression FROM {node_field_data} node_field_data INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid LEFT JOIN {workbench_revision_tracker} workbench_revision_tracker ON node_field_data.nid = workbench_revision_tracker.entity_id AND (workbench_revision_tracker.entity_type = :views_join_condition_0 AND workbench_revision_tracker.langcode = node_field_data.langcode) LEFT JOIN {node_field_revision} node_field_revision_workbench_revision_tracker ON workbench_revision_tracker.revision_id = node_field_revision_workbench_revision_tracker.vid AND (workbench_revision_tracker.entity_type = :views_join_condition_2 AND node_field_revision_workbench_revision_tracker.langcode = workbench_revision_tracker.langcode) WHERE (( (node_field_data.status = 1 OR (node_field_data.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) ))) subquery; Array ( [:views_join_condition_0] => node [:views_join_condition_2] => node ) in Drupal\views\Plugin\views\query\Sql->execute() (line 1452 of core/modules/views/src/Plugin/views/query/Sql.php).

Looks like the workbench_revision_tracker table isn't created until content is created. I get this error when trying to use the relationship when no content has been created which has a workflow attached.

Yeah, I've seen the same issue. I mean there is an issue, but is it really worth to workaround that?

I'd say it's a pretty common error to hit on a fresh install with custom views installed via config (install profiles would be a biggie). Any view using this relationship will WSOD if there is no revisioned content. In my case that's about 7 views.

Actually the fix for this is really easy assuming it's the correct way. Adding $this->ensureTableExists() to the constructor of RevisionTracker.php solves the problem.

I'd like to double check the site-builder impact of this issue: it's not currently possible for editors to see "content needing review" in a view?

Applying this patch and then adding the field to admin/content results in #2738051: \Drupal\views\Plugin\views\query\Sql::getCacheTags and getCacheMaxAge don't take into account that some entities can be NULL

The query generated looks good:

SELECT node_field_data.nid AS nid, users_field_data_node_field_data.uid AS users_field_data_node_field_data_uid, node_field_revision_workbench_revision_tracker.vid AS node_field_revision_workbench_revision_tracker_vid
FROM 
{node_field_data} node_field_data
INNER JOIN {users_field_data} users_field_data_node_field_data ON node_field_data.uid = users_field_data_node_field_data.uid
LEFT JOIN {workbench_revision_tracker} workbench_revision_tracker ON node_field_data.nid = workbench_revision_tracker.entity_id AND (workbench_revision_tracker.entity_type = 'node' AND workbench_revision_tracker.langcode = node_field_data.langcode)
LEFT JOIN {node_field_revision} node_field_revision_workbench_revision_tracker ON workbench_revision_tracker.revision_id = node_field_revision_workbench_revision_tracker.vid AND (workbench_revision_tracker.entity_type = 'node' AND node_field_revision_workbench_revision_tracker.langcode = workbench_revision_tracker.langcode)
WHERE (( (node_field_data.status = 1 OR (node_field_data.uid = 1 AND 1 <> 0 AND 1 = 1) OR 1 = 1) ))
ORDER BY node_field_data.changed DESC
LIMIT 50 OFFSET 0

Committed and pushed, thanks!

Thank you @larowlan!!