0

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?

3 Answers 3

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.

1
  • It's kind of workaround, but it changes the result. In my case I get 15k rows instead of 500, most of them are redundant for me (same e.field1, different e.field2).
    – PolGraphic
    Jul 13, 2016 at 19:48
1

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"

0

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.

3
  • In other words, Drupal limits my possibilities by forcing strict SQL mode (I am aware that returned data will be arbitrary / belongs to one of grouped entities, without guarantee from which one it has been taken - and I am more then ok with that in my plugin). Is there a way to turn it of and still use Drupal? Maybe turn it off for single query?
    – PolGraphic
    Jul 13, 2016 at 19:44
  • No, Drupal doesn't limit your possibilities, it just forces you to write correct SQL. This new settings is enabled by default in newer MySQL versions, Drupal makes sure that code you write with one version doesn't suddenly fail on another server. And the documentation on all this is very good dev.mysql.com/doc/refman/5.7/en/group-by-handling.html, for example mentiones an ANY_VALUE() function that you can use with addExpression() .
    – Berdir
    Jul 13, 2016 at 19:54
  • 1
    one calls it "feature", another one "limit". Personally, I don't like "forced help" in "only good direction" - I want to choose it or not depending on my situation - and the way Drupal "helps" caused many D7 queries to fail suddenly on D8. Anyway, thank you for the link to ANY_VALUE()! :)
    – PolGraphic
    Jul 13, 2016 at 20:06

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.