How do I query for content that has not been updated (no revisions)?

Question

I am creating an API endpoint that serves as a content Feed that pulls in content according to some rules.

One group of content that I want to pull is articles that have been updated and that have been approved to be included in the feed. My approach for this was to include a boolean field for including it in the feed, and query for the number of revisions.

$firstGroup = $query->andConditionGroup()
    ->condition('type', ['article', 'video_game'], 'IN')
    ->condition('status', NodeInterface::PUBLISHED)
    ->condition('field_include_in_feed', 1)
    ->condition('revision_uid', '1', '>=');

$nids = $query->condition($firstGroup)->condition($secondGroup)->execute();

Based on this page from the documentation, I thought revision_uid would be a valid field, but I get an error that it is not. Likewise I get an error, if I use revision as well.

The error is a Drupal QueryException with the message 'revision_uid' not found.

What is the best way to query for content (with entityQuery) that has been updated if I can't use the revision fields?

Answer 1

Content that has not been updated would only have one revision, so you could count the number of revisions and use a having condition.

First you'll have to join the node_revision table to the node table

$query->leftJoin('node_revision', 'r', 'n.nid = r.nid');

Count the number of revisions

$query->addExpression('COUNT(n.nid)', 'revision_count');

Filter rows with a revision_count higher than 1

$query->havingCondition('revision_count', 1, '<=');

Answer 2

Every entity stores a timestamp for when the node was created, as well as another for when the node was last updated. So, ideally, you could add a condition to your EntityQuery checking to see if the created value matches the updated value.

Unfortunately, the standard condition() syntax can't handle this. Drupal 7 gave us the where() syntax to execute arbitrary SQL that the Entity Query system couldn't handle, but this is not available in Drupal 8. Therefore, you have a couple of options as I see it:

1. Use a subquery to pull the created value, then use that returned value as the comparison value against updated in the main query.
2. Abandon (unfortunately) EntityQuery and use a dynamic or static query instead.

EntityQuery has some restrictions that are hard to get around, and field-to-field comparison is definitely one of them. Unfortunately, I don't know any other way to know definitively that an entity has been modified without revision info.