ssossossosso

Forums

Covering OroCRM topics, including community updates and company announcements.  Subscribe

This topic contains 4 replies, has 2 voices, and was last updated by  archy_bold 1 year, 7 months ago.

  • Creator
    Topic
  • #17491

    archy_bold
    Participant

    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:

    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

Viewing 4 replies - 1 through 4 (of 4 total)
  • Author
    Replies
  • #17492
    Yevhen Shyshkin
    Yevhen Shyshkin
    Oro Core

    Hello, Simon.

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

    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.

    #17493

    archy_bold
    Participant

    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:

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

    #17494
    Yevhen Shyshkin
    Yevhen Shyshkin
    Oro Core

    > 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.

    #17495

    archy_bold
    Participant

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

Viewing 4 replies - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.