如何在DQL中替换SQL的IF函数？Symfony3.4+Doctrine报错解决

Hey there, let's work through this issue you're hitting. The error pops up because Doctrine's DQL doesn't natively support MySQL's IF() function — it's a database-specific utility, not part of the standard DQL syntax Doctrine expects.

Here are three solid solutions to get your query working smoothly:

1. Replace IF() with Standard CASE WHEN (Recommended) #

The simplest, most maintainable fix is to swap out the MySQL IF() calls with CASE WHEN, which is standard SQL and fully supported by Doctrine. Here's your updated query:

SELECT DISTINCT le.secteur, le.dept, le.commune, le.sigle, le.rne, le.denomination, le.effectif,
sum(CASE WHEN b.etat = 'Toutes_periodes' THEN b.nb ELSE 0 END) AS toutes_periodes,
sum(CASE WHEN b.etat = 'P1' THEN b.nb ELSE 0 END) AS dont_T1,
sum(CASE WHEN b.etat = 'P2' THEN b.nb ELSE 0 END) AS dont_T2,
sum(CASE WHEN b.etat = 'P3' THEN b.nb ELSE 0 END) AS dont_T3,
sum(CASE WHEN b.etat = 'Cycle' THEN b.nb ELSE 0 END) AS bilan_cycle
FROM list_etabs le
LEFT OUTER JOIN bilan b on le.rne = b.rne
GROUP BY le.secteur, le.dept, le.commune, le.sigle, le.rne
ORDER BY le.secteur, le.dept, le.commune, le.sigle, le.rne

This does exactly the same logic as your original query, but uses syntax Doctrine understands without any extra setup.

2. Use a Native SQL Query #

If you really want to keep using IF(), you can bypass DQL entirely and execute a native MySQL query directly in your repository:

// Inside your Entity Repository class
public function getEtabsBilan()
{
    $em = $this->getEntityManager();
    $sql = "SELECT DISTINCT le.secteur, le.dept, le.commune, le.sigle, le.rne, le.denomination, le.effectif, 
            sum(IF(b.etat = 'Toutes_periodes', b.nb, 0)) AS toutes_periodes, 
            sum(IF(b.etat = 'P1', b.nb, 0)) AS dont_T1, 
            sum(IF(b.etat = 'P2', b.nb, 0)) AS dont_T2, 
            sum(IF(b.etat = 'P3', b.nb, 0)) AS dont_T3, 
            sum(IF(b.etat = 'Cycle', b.nb, 0)) AS bilan_cycle 
            FROM list_etabs le 
            LEFT OUTER JOIN bilan b on le.rne = b.rne 
            GROUP BY le.secteur, le.dept, le.commune, le.sigle, le.rne 
            ORDER BY le.secteur, le.dept, le.commune, le.sigle, le.rne";
    
    // If mapping results to an entity, set up a ResultSetMapping
    $rsm = new \Doctrine\ORM\Query\ResultSetMapping();
    // Example mapping: $rsm->addScalarResult('secteur', 'secteur');
    
    $query = $em->createNativeQuery($sql, $rsm);
    return $query->getResult();
}

Note: Use getScalarResult() instead of getResult() if you just need raw data without entity mapping.

3. Register a Custom DQL Function for IF() #

If you plan to use IF() frequently in DQL queries, you can register it as a custom function so Doctrine recognizes it:

Step 1: Create the Custom Function Class #

Make a new file src/Doctrine/Functions/IfFunction.php with this code:

<?php
namespace App\Doctrine\Functions;

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

class IfFunction extends FunctionNode
{
    public $condition;
    public $thenExpr;
    public $elseExpr;

    public function parse(Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->condition = $parser->ConditionalExpression();
        $parser->match(Lexer::T_COMMA);
        $this->thenExpr = $parser->ScalarExpression();
        $parser->match(Lexer::T_COMMA);
        $this->elseExpr = $parser->ScalarExpression();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }

    public function getSql(SqlWalker $sqlWalker)
    {
        return sprintf(
            'IF(%s, %s, %s)',
            $this->condition->dispatch($sqlWalker),
            $this->thenExpr->dispatch($sqlWalker),
            $this->elseExpr->dispatch($sqlWalker)
        );
    }
}

Step 2: Configure Doctrine to Use the Function #

Add this to your app/config/config.yml file:

doctrine:
    orm:
        entity_managers:
            default:
                dql:
                    string_functions:
                        IF: App\Doctrine\Functions\IfFunction

Now you can use IF() directly in your DQL queries just like you did in your original SQL.

内容的提问来源于stack exchange，提问作者cyphos