[Bug] Datarid Filters, filter_by_having Not Working

Hi there. I’ve come across a weird scenario where one of my datagrid filters doesn’t work and it seems to be because filter_by_having isn’t working correctly, at least in my case. Here’s the important parts from my datagrid.yml file:

YAML

datagrid: grid-name: extended_entity_name: %customer_entity% source: type: orm query: select: ... - GROUP_CONCAT(orders.orderReference SEPARATOR ' ') AS orderReferences from: - { table: %customer_entity%, alias: c } join: left: - { join: c.orders, alias: orders } filters: columns: orderReferences: type: string data_name: orderReferences filter_by_having: true

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

datagrid:     grid-name:         extended_entity_name: %customer_entity%         source:             type: orm             query:                 select:                     ...                     - GROUP_CONCAT(orders.orderReference SEPARATOR ' ') AS orderReferences                 from:                     - { table: %customer_entity%, alias: c }                 join:                     left:                         - { join: c.orders, alias: orders }         filters:             columns:                 orderReferences:                     type: string                     data_name: orderReferences                     filter_by_having: true

So, as you can see, I’m trying to get all the order references for each customer into a column and then using that to filter on.

All seems to work well until I try to filter and I get the following error:

An exception occurred while executing 'SELECT count(t0_.id) AS sclr_0 FROM trueconn_customer t0_ LEFT JOIN trueconn_order t1_ ON t0_.id = t1_.customer_id AND (t1_.organization_id = 1) WHERE GROUP_CONCAT(t1_.order_reference SEPARATOR ' ') LIKE ? AND t0_.organization_id = 1' with params ["%uns%"]:

SQLSTATE[HY000]: General error: 1111 Invalid use of group function (500 Internal Server Error)

This query would work if the GROUP_CONCAT part was part of a HAVING statement rather than a WHERE, which I would think filter_by_having would do.

Is it a bug or is there more I’m missing?

Thanks,
Simon

Topic

[Yevhen ShyshkinParticipant]

Hello, Simon.

This is really weird case – I’ve tried to do exactly the same and it works for me:

YAML

filters: columns: firstName: type: string data_name: accountUser.firstName filter_by_having: true

1 2 3 4 5 6

filters:     columns:         firstName:             type:      string             data_name: accountUser.firstName             filter_by_having: true

PgSQL

SELECT o0_.id AS id_0, o0_.first_name AS first_name_1, o0_.last_name AS last_name_2, o0_.email AS email_3, o0_.enabled AS enabled_4, o0_.confirmed AS confirmed_5 FROM orob2b_account_user o0_ WHERE o0_.organization_id = 1 HAVING o0_.first_name LIKE ? ORDER BY o0_.first_name ASC' with params ["%\u0424\u044c%"]

1 2 3 4 5 6

SELECT o0_.id AS id_0, o0_.first_name AS first_name_1, o0_.last_name AS last_name_2, o0_.email AS email_3, o0_.enabled AS enabled_4, o0_.confirmed AS confirmed_5 FROM orob2b_account_user o0_ WHERE o0_.organization_id = 1 HAVING o0_.first_name LIKE ? ORDER BY o0_.first_name ASC' with params ["%\u0424\u044c%"]

Please, make sure that cache is fresh (do app/console cache:clear in the appropriate environment) and try again. If issue still will be there – I’d like to ask for full grid configuration to reproduce this issue locally.

[archy_boldParticipant]

Thanks for the response Yevhen. I managed to solve it, actually. I dug a little deeper and saw it was working for me when using a COUNT. The difference here was that I added a groupBy to the datagrid configuration. So my datagrid looks as follows now:

YAML

datagrid: grid-name: extended_entity_name: %customer_entity% source: type: orm query: select: ... - GROUP_CONCAT(orders.orderReference SEPARATOR ' ') AS orderReferences from: - { table: %customer_entity%, alias: c } join: left: - { join: c.orders, alias: orders } groupBy: c.id filters: columns: orderReferences: type: string data_name: orderReferences filter_by_having: true

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

datagrid:     grid-name:         extended_entity_name: %customer_entity%         source:             type: orm             query:                 select:                     ...                     - GROUP_CONCAT(orders.orderReference SEPARATOR ' ') AS orderReferences                 from:                     - { table: %customer_entity%, alias: c }                 join:                     left:                         - { join: c.orders, alias: orders }                 groupBy: c.id         filters:             columns:                 orderReferences:                     type: string                     data_name: orderReferences                     filter_by_having: true

I guess you need to group your results in the case of using an aggregate function?

[Yevhen ShyshkinParticipant]

> I guess you need to group your results in the case of using an aggregate function?
Yes, of course. MySQL maybe is a bit less strict in this case, but PostgreSQL will trigger an exception if you try to use having without grouping.

[archy_boldParticipant]

Ah, my stupidity there! Thanks for looking into it.

[Author]

Replies