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

    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:

  ->aggregate('field_industries.delta', 'COUNT')
  ->conditionAggregate('field_industries.delta', 'COUNT', 1, '>')

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)?

1 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->having('COUNT(*) > 1');

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

Hope this helps

  • The question is to use "an aggregated entity query", that is not an entity query.
    – LarS
    Mar 29, 2019 at 17:08
  • Then give a better alternative instead of down voting. The above answer does what OP asks.
    – Robin
    Apr 9, 2019 at 11:09
  • It is not an ENTITY query, it is a DATABASE query. I was looking for information on how to aggregate entity queries, the answer was not helpful. But yes you are right, I should have provided a better answer.
    – LarS
    Apr 9, 2019 at 22:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.