How to use GROUP_BY with a query builder and mySQL >= 5.7?

Question

After migrating my database to CloudDB with Mysql 5.7 version I have problem with query builder in symfony 4.

The following code works great with mysql 5.6

public function findRecentReviews($max): array
{
    $qb = $this->createQueryBuilder('r')
        ->andWhere('r.active = :active')
        ->setParameter('active', 1)
        ->setMaxResults($max)
        ->groupBy('r.website')
        ->orderBy('r.id', 'ASC')
        ->getQuery();

    return $qb->execute();
}

With 5.7 I'm getting this error

An exception has been thrown during the rendering of a template ("An >exception occurred while executing 'SELECT r0_.id AS id_0, r0_.comment AS >comment_1, r0_.author AS author_2, r0_.email AS email_3, r0_.added AS >added_4, r0_.ip AS ip_5, r0_.active AS active_6, r0_.hide_in_latest AS >hide_in_latest_7, r0_.rate AS rate_8, r0_.website_id AS website_id_9 FROM >review r0_ WHERE r0_.active = ? GROUP BY r0_.website_id ORDER BY r0_.id ASC >LIMIT 3' with params [1]:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of >SELECT list is not in GROUP BY clause and contains nonaggregated column >'ps_main.r0_.id' which is not functionally dependent on columns in GROUP BY >clause; this is incompatible with sql_mode=only_full_group_by").*

I can't disable ONLY_FULL_GROUP_BY mode because I don't have a superuser privileges.

How should look like my query if I can't disable ONLY_FULL_GROUP_BY and I can't downgrade a mysql version?

Why are you currently using group by? I don't see any aggregated columns. Are you just using it to get a distinct list of websites? — Don't Panic, Sep 04 '19 at 14:26
Sorry, I didn't notice the name of the function previously. Looks like the group by is there so you'll only get one review for each website? — Don't Panic, Sep 04 '19 at 14:33
I think this looks like a [tag:greatest-n-per-group] problem. This Q&A may be helpful: https://stackoverflow.com/questions/46106582/doctrine-query-language-get-max-latest-row-per-group — Don't Panic, Sep 04 '19 at 14:48

Dark Knight · Answer 1 · 2019-09-04T15:28:02.540

0

Reference: MySQL Handling of Group By

MySQL 5.7.5 and up implements detection of functional dependence.

If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

There are two options.

1: Disable SQL mode only_full_group_by: You can check this by executing below query:

SHOW VARIABLES LIKE '%sql_mode%';

Output:
sql_mode     'STRICT_ALL_TABLES,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY'

Disable only_full_group_by in mysql by removing ONLY_FULL_GROUP_BY from sql_mode variable.

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

2: Update your query:

SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;

Reference: Detection of Functional Dependence

edited Sep 04 '19 at 15:28

answered Sep 04 '19 at 14:30

Dark Knight

6,116
1
15
37

OP states in the question that they can't disable ONLY_FULL_GROUP_BY. Are they mistaken? If so, can you explain why? I'm not meaning to be critical, I'm just curious. – Don't Panic Sep 04 '19 at 14:41
Unfortunetly, like I said I can't change this beucase I don't have superuser priviliges. I'm getting "42000][1227] Access denied; you need (at least one of) the SUPER privilege(s) for this operation" so the only solution is to change my query in doctrine. – Szymon Sep 04 '19 at 14:43
2

@Szymon every user can alter the session thread values including `ONLY_FULL_GROUP_BY` the `SET SESSION` one should work the `GLOBAL` indeed requires `SUPER` priv.. But i advice to fix the query – Raymond Nijland Sep 04 '19 at 14:44
1

Keep in mind that `SELECT name, ANY_VALUE(address), MAX(age) FROM t GROUP BY name;` more or less runs like (extreme example) in the optimizer as `SELECT name, (SELECT address FROM t t_inner WHERE t_inner.name = t.name ORDER BY RAND() LIMIT 1) AS 'ANY_VALUE(address)', MAX(age) FROM t GROUP BY name;` .. i would advice more that the topicstarter would fix the query.. – Raymond Nijland Sep 04 '19 at 15:11
I think, that I can't use ANY_VALUE in doctrine query builder. Maybe I will use a pure SQL query Instead. – Szymon Sep 04 '19 at 15:13

How to use GROUP_BY with a query builder and mySQL >= 5.7?

1 Answers1