custom SQL in Oro Report "functions"?

When I access the page ‘/report/update/[id]’, I can set a custom function that I coded for date/datetime columns. For that, I created a custom bundle the following files:

YAML

# MyBundle/Resources/config/oro/query_designer.yml query_designer: aggregates: date_month_name: applicable: [{type: date}, {type: datetime}] functions: - { name: Month name, expr: EXTRACT(MONTH FROM $column) } query_type: [report]

1 2 3 4 5 6 7 8 9

# MyBundle/Resources/config/oro/query_designer.yml   query_designer:     aggregates:         date_month_name:             applicable: [{type: date}, {type: datetime}]             functions:                 - { name: Month name, expr: EXTRACT(MONTH FROM $column) }             query_type: [report]

PHP

# MyBundle/Resources/translations/messages.pt_BR.yml ibv: query_designer: aggregates: date_month_name: Month name: name: Nome do mês

1 2 3 4 5 6 7 8

# MyBundle/Resources/translations/messages.pt_BR.yml   ibv:     query_designer:         aggregates:             date_month_name:                 Month name:                     name: Nome do mês

Then, in Oro UI, I select this function for my date/datetime field (see the image below):

And when I try to save the report, appears a error message with this content:

How do I execute custom SQL command in my custom ‘functions’ in Oro Reports, beyond the default functions ‘SUM’, ‘AVG’, ‘MIN’, ‘MAX’, and so on?

• This topic was modified 5 years, 12 months ago by  spyout98.

Topic

[Andrey YatsenkoModerator]

You can see the full list of registered custom doctrine functions here https://github.com/oroinc/platform/blob/master/src/Oro/Bundle/EntityBundle/Resources/config/oro/app.yml#L17

We use https://github.com/oroinc/doctrine-extensions for all custom functions, you can check the code there in case you will want to create custom function.

[spyout98Participant]

Andrey,

Thank you so much for feedback! I had no idea that Oro implements doctrine extensions, It’s good to know about it.

I will try to use some of the functions listed in this list (https://github.com/oroinc/doctrine-extensions#dql-functions) and I’ll give some feedback here in this topic about this.

Thanks again!

PS: I am using PostgreSQL 9.6.

• This reply was modified 5 years, 11 months ago by  spyout98.

[spyout98Participant]

Andrey, this worked well! Thank you for your help!

I think that this kind of feature must be useful for all community that uses Oro. I will make a pull request for OroPlatform to suggest to incorporate these new “functions”. I will post the link of Github here later.

• This reply was modified 5 years, 11 months ago by  spyout98.

[Author]

Replies