Get entities grouped by year

Question

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?

Answer 1

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();

Answer 2

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.