Using an aggregated entity query to find duplicate taxonomy terms

Question

Due to a content migration bug, there are nodes in our site that reference the same taxonomy term multiple times with different deltas:

bundle  deleted entity_id   revision_id langcode    delta   field_term_target_id
article 0   1061    1062    en  0   307
article 0   1061    1062    en  1   307

I can find these with the following database query

SELECT
    entity_id, field_term_target_id, COUNT(*) as count
FROM node__field_industries
GROUP BY entity_id, field_industries_target_id
HAVING count > 1;

For building a tool that lists these nodes within Drupal, I would like to use an entity query instead of accessing the database directly.

I've tried the following:

\Drupal::entityTypeManager()
  ->getStorage('node')
  ->getAggregateQuery()
  ->groupBy('nid')
  ->groupBy('field_industries')
  ->aggregate('field_industries.delta', 'COUNT')
  ->conditionAggregate('field_industries.delta', 'COUNT', 1, '>')
  ->execute();

But this results in an error Invalid specifier 'delta'. If I aggregate on 'field_industries' instead (which seems wrong, as I'm already grouping on that value), I get completely wrong results back (specifically, vastly inflated values for COUNT, which can be as high as 16 even for nodes that are not affected by the bug).

So I guess my question is: How do I aggregate on a non-default column in the field table (such as the delta column)?

Answer 1

You can use the Database abstraction layer:

$connection = Drupal::database();
$query = $connection->select('node__field_industries', 'i');
$query->fields('i', ['entity_id', 'field_term_target_id']);
$query->addExpression('COUNT(*)', 'count');
$query->groupBy('i.entity_id');
$query->groupBy('i.field_term_target_id');
$query->having('COUNT(*) > 1');

$results = $query->execute()->fetchAll();

Hope this helps