when I try to write a dql query there is an error for lag & over ( beberlei/DoctrineExtensions )

Question

I'm trying to convert a MySQL8 query to Doctrine DQL in Symfony4. I used to https://github.com/beberlei/DoctrineExtensions extensions. But there are still errors.

Query is work well.

SELECT id, machine_amount, LAG(machine_amount) OVER ( PARTITION BY machine_id ORDER BY id ) AS prevField FROM machine_income

repositoryClass

$q = $this->createQueryBuilder('mi');

$q->select('mi.id, mi.machineAmount');
$q->addSelect('LAG(mi.machineAmount) OVER (PARTITION BY mi.machine ORDER BY mi.id) AS prevField');

return $q->getQuery()->getSQL();

doctrine.yaml

doctrine:
    orm:
        dql:
            string_functions:
                lag: DoctrineExtensions\Query\Mysql\Lag
                over: DoctrineExtensions\Query\Mysql\Over

DQL out

SELECT mi.id, mi.machineAmount, LAG(mi.machineAmount) OVER (PARTITION BY mi.machine ORDER BY mi.id) AS prevField FROM App\Entity\MachineIncome mi

When I try to create getSQL() I saw error.

error

[Syntax Error] line 0, col 59: Error: Expected Doctrine\ORM\Query\Lexer::T_FROM, got '('

any help?

score 1 · Answer 1 · answered Nov 23 '20 at 13:11

Judging from the implementation, this extension only supports the following syntax:

OVER( arithmeticExpression [, order by clause])

So, no PARTITION BY.
You probably need something like this:
https://github.com/elshafey/doctrine-window-functions

Or pick from the answers to this other question:
Can I use window functions in doctrine 2?

when I try to write a dql query there is an error for lag & over ( beberlei/DoctrineExtensions )

1 Answers1