Can I use window functions in doctrine 2?

Question

SELECT invoice.id, 
COUNT(slip.id),
SUM(projected_minutes)  OVER (PARTITION BY task.id) AS projected_minutes
FROM invoice
INNER JOIN task ON task.invoice_id = invoice.id
LEFT JOIN slip ON slip.task_id = task.id

The query above is in postgresql, and I want to convert it to DQL, but I cant find any documentation for window functions in DQL, is this natively supported in doctrine or would i have to create a custom dql function for this?

score 4 · Accepted Answer · answered Mar 26 '13 at 20:39

4

There is no support for this vendor specific function in Doctrine. Either create a custom DQL function or use Native SQL.

answered Mar 26 '13 at 20:39

Elnur Abdurrakhimov

44,533
10
148
133

Was afraid of that, tried searching for custom DQL functions for this, but came up empty, guess I'll write it, just didnt want to reinvent the wheel. – Trololololol Mar 26 '13 at 20:50
3

`OVER` isn't exactly vendor specific unless "vendor specific" means "standard SQL that MySQL doesn't understand". – mu is too short Mar 26 '13 at 21:08
@muistooshort let me rephrase: "minimum common denominator across vendors" - looks better, no? – Ocramius Mar 27 '13 at 03:44
Window functions are part of SQL proper ;) – przemo_li Oct 16 '15 at 06:36
came here looking for the same thing. If someone has writting this custom DQL, it would save work. – Tom Nov 22 '18 at 14:47
@Tom I've added an example in another answer – Khriz Dec 03 '18 at 11:57

score 2 · Answer 2 · answered Dec 03 '18 at 11:57

As asked in one of the comments, I attach my custom string function for COUNT() OVER()

<?php

namespace Example\Doctrine\CustomFunctions;

use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;

/**
 * Class CountOverSql
 */
class CountOverSql extends FunctionNode
{
    /**
     * @var string
     */
    private $field;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return "COUNT(".$this->field->dispatch($sqlWalker).") OVER()";
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser): void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

and you only need to add this code to your doctrine configuration:

$entityManager->getConfiguration()->addCustomStringFunction('count_over', function ($name) use ($c) {
    return new Example\Doctrine\CustomFunctions\CountOverSql($name);
});

And to use it, add this code to your select statement:

$queryBuilder->select('table_name', 'count_over(table_name.id)');

Hope it helps someone.

score 1 · Answer 3 · answered Mar 21 '19 at 19:19

Slightly different version of window function class. It's more universal, because you can use here any aggregate function + use "Order By" clause in window.

use Doctrine\ORM\Query\AST\AggregateExpression;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\AST\OrderByClause;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\SqlWalker;

class Over extends FunctionNode
{
    /** @var AggregateExpression */
    private $functionExpr;

    /** @var OrderByClause */
    private $orderByExpr;

    /**
     * {@inheritdoc}
     *
     * @throws \Doctrine\ORM\Query\AST\ASTException
     */
    public function getSql(SqlWalker $sqlWalker): string
    {
        if (!empty($this->orderByExpr->orderByItems)) {
            return "{$this->functionExpr->dispatch($sqlWalker)} OVER ({$this->orderByExpr->dispatch($sqlWalker)})";
        }

        return "{$this->functionExpr->dispatch($sqlWalker)} OVER ()";
    }

    /**
     * @param Parser $parser
     *
     * @throws \Doctrine\ORM\Query\QueryException
     */
    public function parse(Parser $parser): void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->functionExpr = $parser->StringPrimary();
        $parser->match(Lexer::T_COMMA);
        $this->orderByExpr = $parser->OrderByClause();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

And use it like this:

$aggregateQueryResult = $this->createQueryBuilder('agg')
      ->select('OVER(sum(agg.quantity), ORDER BY agg.date, agg.id) as aggregated_sum')

score 0 · Answer 4 · answered Sep 19 '20 at 23:52

Here is an extension which provide widow functions functionality in Doctrine 2 https://github.com/elshafey/doctrine-window-functions

You can use the extension as following

// configure the extension first
$entityManager->getConfiguration()->addCustomStringFunction(
    'WINDOW',
    \Elshafey\DoctrineExtensions\WindowFunctions\Query\Mysql\Window::class
);

// use your window function formula
SELECT invoice.id, 
COUNT(slip.id),
WINDOW(SUM(projected_minutes))  OVER (PARTITION BY task.id) AS projected_minutes
FROM invoice
INNER JOIN task ON task.invoice_id = invoice.id
LEFT JOIN slip ON slip.task_id = task.id

Can I use window functions in doctrine 2?

4 Answers4

Linked