Hello,
I'm not able to select more than one field when using groupBy.
Example:
This Works
$query = $this->database->select('taxonomy_term_field_data', 'ttdf');
$query->fields('ttdf', ['tid'])
->groupBy('ttdf.tid');
$data = $query->execute();
$results = $data->fetchAll();
This doesn't work:
$query = $this->database->select('taxonomy_term_field_data', 'ttdf');
$query->fields('ttdf', ['tid', 'name'])
->groupBy('ttdf.tid');
$data = $query->execute();
$results = $data->fetchAll();
ERROR:
The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 database.ttdf.name isn't in GROUP BY: SELECT ttdf.tid AS tid, ttdf.name AS name
FROM
{taxonomy_term_field_data} ttdf
GROUP BY ttdf.tid; Array
(
)
This works:
(Adding the other field to the groupby)
$query = $this->database->select('taxonomy_term_field_data', 'ttdf');
$query->fields('ttdf', ['tid', 'name'])
->groupBy('ttdf.tid, ttdf.name');
$data = $query->execute();
$results = $data->fetchAll();
What if we need to select multiple fields. Are we required to add them all to the groupBy?
NOTE: If I execute a query againts mysql, I can achieve selecting all desired fields without having to include all of them in the groupBy. So this is not a MySQL limitation.
Thanks in advance.
Not all content is available!
It's likely this issue predates Contrib.social: some issue and comment data are missing.
I experienced the same issue on D9.5 even with the addition of #6. This worked fine on D9.4.
After a bit of debugging, I was able to solve this issue by adding each groupBy field separately to the query.
For example,
$query->groupBy('ttdf.tid, ttdf.name');
now became,
$query->groupBy('ttdf.tid');
$query->groupBy('ttdf.name');