Forums › Forums › OroPlatform › OroPlatform – Programming Questions › DataGrid Bundle –
This topic contains 5 replies, has 2 voices, and was last updated by Denis 6 years, 10 months ago.
Starting from March 1, 2020 the forum has been switched to the read-only mode. Please head to StackOverflow for support.
- CreatorTopic
- May 3, 2017 at 7:44 am #34382
Please help me.
Can I use the RANK() or DENSE_RANK() functions in source of datagrid config?
When I try I have an error
An exception has been thrown during the rendering of a template ([Syntax Error] line 0, col 144: Error: Expected known function, got “DENSE_RANK”)Thanks.
- CreatorTopic
- AuthorReplies
- May 4, 2017 at 8:22 am #34383
Hi
If you are using mysql, as far i know it doesn’t support these functions, but i found many articles where described how to emulate this functions.
May 4, 2017 at 11:56 pm #34384Thanks Mike
Sorry I did not clarify my question (
I use Postgres
and I want to have Rank in datagrid
for example like thisquery:
select:
– product.brand
– product.name
– (SUM(product.totalcost)) as totalcost
– (DENSE_RANK() OVER (ORDER BY SUM(product.totalcost) DESC)) as rank
from:
– { table: Extend\Entity\product, alias: product }It works for Postgres bat does not work for datagrid.
May 8, 2017 at 10:41 am #34385Hi
The doctrine by default doesn’t know about this function, but you can define it in your bundle via Resources/config/oro/app.yml (OroCRM 2.* version)
1. Example of simple function, without DQL definition (function with only one ArithmeticPrimary argument):
– src/Custom/Bundle/OroBundle/Resources/config/oro/app.yml
YAML12345doctrine:orm:dql:string_functions:test: Oro\ORM\Query\AST\Functions\SimpleFunction– SQL definition: src/Oro/ORM/Query/AST/Platform/Functions/Mysql/Test.php
PHP123456789101112131415161718192021<?phpnamespace Oro\ORM\Query\AST\Platform\Functions\Mysql;use Doctrine\ORM\Query\AST\Node;use Doctrine\ORM\Query\SqlWalker;use Oro\ORM\Query\AST\Functions\SimpleFunction;use Oro\ORM\Query\AST\Platform\Functions\PlatformFunctionNode;class Test extends PlatformFunctionNode{/*** {@inheritdoc}*/public function getSql(SqlWalker $sqlWalker){/** @var Node $expression */$expression = $this->parameters[SimpleFunction::PARAMETER_KEY];return 'UPPER(' . $this->getExpressionValue($expression, $sqlWalker) . ')';}}2. Example of complex function, with DQL definition:
– src/Custom/Bundle/OroBundle/Resources/config/oro/app.yml
YAML12345doctrine:orm:dql:datetime_functions:convert_tz: Oro\ORM\Query\AST\Functions\DateTime\ConvertTz– DQL definition: src/Oro/ORM/Query/AST/Functions/DateTime/ConvertTz.php:
PHP1234567891011121314151617181920212223242526272829<?phpnamespace Oro\ORM\Query\AST\Functions\DateTime;use Doctrine\ORM\Query\Parser;use Doctrine\ORM\Query\Lexer;use Oro\ORM\Query\AST\Functions\AbstractPlatformAwareFunctionNode;class ConvertTz extends AbstractPlatformAwareFunctionNode{const VALUE_KEY = 'value';const FROM_TZ_KEY = 'from_tz';const TO_TZ_KEY = 'to_tz';/*** {@inheritdoc}*/public function parse(Parser $parser){$parser->match(Lexer::T_IDENTIFIER);$parser->match(Lexer::T_OPEN_PARENTHESIS);$this->parameters[self::VALUE_KEY] = $parser->ArithmeticPrimary();$parser->match(Lexer::T_COMMA);$this->parameters[self::FROM_TZ_KEY] = $parser->ArithmeticPrimary();$parser->match(Lexer::T_COMMA);$this->parameters[self::TO_TZ_KEY] = $parser->ArithmeticPrimary();$parser->match(Lexer::T_CLOSE_PARENTHESIS);}}SQL definition: src/Oro/ORM/Query/AST/Platform/Functions/Mysql/ConvertTz.php:
PHP1234567891011121314151617181920212223242526272829303132<?phpnamespace Oro\ORM\Query\AST\Platform\Functions\Mysql;use Doctrine\ORM\Query\AST\Node;use Doctrine\ORM\Query\SqlWalker;use Oro\ORM\Query\AST\Functions\DateTime\ConvertTz as BaseFunction;use Oro\ORM\Query\AST\Platform\Functions\PlatformFunctionNode;class ConvertTz extends PlatformFunctionNode{/*** {@inheritdoc}*/public function getSql(SqlWalker $sqlWalker){/** @var Node $value */$value = $this->parameters[BaseFunction::VALUE_KEY];/** @var Node $fromTz */$fromTz = $this->parameters[BaseFunction::FROM_TZ_KEY];/** @var Node $toTz */$toTz = $this->parameters[BaseFunction::TO_TZ_KEY];return 'CONVERT_TZ('. $this->getExpressionValue($value, $sqlWalker). ', '. $this->getExpressionValue($fromTz, $sqlWalker). ', '. $this->getExpressionValue($toTz, $sqlWalker). ')';}}I think you should use second variant, also please read the documentation about adding new functions.
May 8, 2017 at 9:43 pm #34386There you can find all functions that were created by our team: vendor/oro/doctrine-extensions/src/Oro/ORM/Query/AST
May 15, 2017 at 4:25 am #34387Thanks Mike
- AuthorReplies
The forum ‘OroPlatform – Programming Questions’ is closed to new topics and replies.