1

Trying to write entity queries with group by condition using Drupal::entityQueryAggregate. It works well for simple fields, for example:

$query = \Drupal::entityQueryAggregate('node')->condition('status', 1)->condition('type', 'node_type');

$result = $query
      ->groupBy('field_custom_field')
      ->aggregate('nid', 'COUNT')
      ->execute();

But now I have to select nodes aggregated by year from custom field and I can't figure out how to do it:

->groupBy('YEAR(field_custom_date)') - doesn't work

->addExpression('YEAR(field_custom_date)', 'fcd_year') - doesn't work

Any ideas how to implement this query?

3

My final solution, by guidance @CG Monroe:

$query = $this->connection->select('node_field_data', 'n');
$query->innerJoin('node__field_date_created', 'fdc', 'fdc.entity_id = n.nid');
$query->addExpression('count(n.nid)', 'nid_count');
$query->addExpression('substring(fdc.field_date_created_value, 1, 4)', 'yr');
$query->condition('n.type', 'my_type');
$query->condition('n.status', 1);
$query->groupBy('yr')->orderBy('yr', 'ASC');
$result = $query->execute()->fetchAll();
1

Drupal has abstracted the DateTime field to a string with an ANSI standard date format. This is part of making Drupal more friendly for non-SQL data storage.

This means the standard SQL date functions will not work.

The date string format is basically YYYY-MM-DDTHH:MM:SS (may vary by granularity).

One solution to using a query to group by year is to add an expression that uses the substring function to get the year from the field and then group by that.

  • Thanks for clarifying. EntityQueryAggregate doesn't support expressions, entityQuery doesn't support grouping. Finally, the only option is to write dynamic query with expression? – stan Dec 19 '17 at 10:03
  • Looks that way, you can add the count aggregation as an expression. I think sql should recognize the count expression as an aggregate. But if you get errors, you may need to add that as a groupby condition as well. – CG Monroe Dec 19 '17 at 14:39
  • Count expression works without group by condition, but in this case it returns just overall count of records, without separation by year or any other condition. Anyway, thanks for help, now I know what to do. – stan Dec 19 '17 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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