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