Selecting more then one column when groupBy is present (db_select) - Drupal 8 added 'ONLY_FULL_GROUP_BY'

Question

In my custom Drupal 8 plug-in I perform such query:

$select = db_select('my_table', 'e');
$select->addField('e', 'field1');

$select->condition('field3', 'NULL', '!=');
$select->condition('field4', array(11, 12, 31, 32, 33), 'IN');

$select->groupBy("e.field1");
$select->orderBy("e.field1");
$select->orderBy("e.field5");
$entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC); 

And it works fine. It returns me all records grouped by field1 that meets the condition, orderered. But it returns only field1 column.

I wanted to get more columns, so I have added another $select->addField('e', 'field2'); just after first one:

$select = db_select('my_table', 'e');
$select->addField('e', 'field1');
$select->addField('e', 'field2');

$select->condition('field3', 'NULL', '!=');
$select->condition('field4', array(11, 12, 31, 32, 33), 'IN');

$select->groupBy("e.field1");
$select->orderBy("e.field1");
$select->orderBy("e.field5");
$entries = $select->execute()->fetchAll(\PDO::FETCH_ASSOC); 

This time I get error:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 Użyto 'my_database.e.field2' bez umieszczenia w group by:

Which means "my_database.e.field2 has been used without putting in into group by". But I don't want to group by every column I select.

Unfortunatelly Drupal 8 added ONLY_FULL_GROUP_BY :( Then, how can I use groupBy and select more then one column at the same time?

Answer 1

I had a similar issue just a few days ago, and the only way to solve it was to add another groupBY query for the extra addField for the e, field2 column:

$select->groupBy("e.field2");

But, both of my groupBY where similar datas, in my case, in one field it was a date in word format (11th July 2016) and the other field group, the data was in epoch time, to help ascend or descend my table.

Answer 2

This is pretty annoying, but it is to provide cross database compatibility for Drupal. You can get around it by adding the following to your database connection array if you only ever intend to live in MySQL compatible land:

'init_commands' => [
  'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
],

But I haven't yet figured out how to disable in my Kernel tests. Still getting something like "PDOException: SQLSTATE[42000]: Syntax error or access violation: 1055 'local.nfd.title' isn't in GROUP BY"

Answer 3

Which means "my_database.e.field2 has been used without putting in into group by". But I don't want to group by every column I select.

Whether or not you want it is not relevant in this case. Drupal enforces a stricter SQL mode in MySQL which requires exactly this. Every selected column must either be part of groupBy or you must use a function like count(), max(), group_concat() or similar to define what value you want.

MySQL out of the box tries to be nice and just returns an arbitrary field2 value for each group but there is no guarantee that it is actually the value you want, other database backends do not.