Question 211452 in PHP Question posted in
Official manual can be found here.

Php – Syntax Error line 0, col 245: Error: Unexpected ')'

This SQL request works perfectly:

SELECT 
    f.idFile,
    f.FileName,
    f.date_file,
    COUNT(DISTINCT (ligne.numLigne)) AS nbCmde,
    COUNT(DISTINCT CASE WHEN bl.date_production != "0000-00-00 00:00:00" THEN ligne.numLigne END) AS cmdProd,
    COUNT(DISTINCT CASE WHEN bl.date_production = "0000-00-00 00:00:00" THEN ligne.numLigne END) AS cmdNonProd,
    COUNT(DISTINCT CASE WHEN tr.`idStatut` = 10 THEN ligne.numLigne END) AS rupture,
    COUNT(DISTINCT CASE WHEN tr.`flag_xport` = TRUE THEN ligne.numLigne END) AS retention,  
    MIN(bl.date_production)AS min_date_production,
    MAX(bl.date_production)  AS max_date_production,
    SUM(cmd.quantite)  AS nbArticles, 
    tr.date_depot
FROM ecomm_lignes AS ligne
INNER JOIN `ecomm_files` AS f ON f.idFile = ligne.idFile
LEFT JOIN ecomm_bl AS bl ON ligne.numBL = bl.BL
INNER JOIN ecomm_tracking AS tr ON ligne.numLigne = tr.numLigne
LEFT JOIN ecomm_cmdep AS cmd ON ligne.numBL = cmd.numBL
INNER JOIN ecomm_appli AS appli ON f.idAppli = appli.idAppli
WHERE appli.idAppli = 774 
AND f.date_file BETWEEN "2023-06-17 00:00:00" AND "2023-07-17 00:00:00"
GROUP BY f.idfile
ORDER BY f.date_file DESC

But when I turn it into a query in Symfony like this:

public function findBlByOpeByFileByDate($idAppli,$dateDebut, $dateFin)  {

    return $this->createQueryBuilder('ligne')
                ->leftJoin('ligne.ecommCmdeps', 'cmd')
                ->innerJoin('ligne.numligne', 'tr')
                ->innerJoin('tr.idfile', 'file')
                ->innerJoin('file.idappli', 'app')
                ->innerJoin('tr.idStatut','statut')
                ->leftJoin('ligne.bls','bl')
                ->where('app.idappli IN (:id)')
                ->setParameter('id', $idAppli)
                ->andWhere('file.dateFile >= :dateDebut')
                ->setParameter('dateDebut', $dateDebut)
                ->andWhere('file.dateFile <= :dateFin')
                ->setParameter('dateFin', $dateFin)
                ->select('file.idfile')
                ->addSelect('file.dateFile')
                ->addSelect('file.filename')
                ->addSelect('COUNT(DISTINCT ligne.numligne) as nbCmde')
                ->addSelect('COUNT(DISTINCT CASE WHEN bl.siteProduction != 0 THEN ligne.numligne END) as cmdProd')
                ->addSelect('MIN(bl.dateProduction) as minDateProd')
                ->addSelect('MAX(bl.dateProduction) as maxDateProd')
                ->addSelect('SUM(cmd.quantite) as nbArticles')
                ->addSelect('tr.dateDepot')
                ->groupBy('file.idfile')
                ->orderBy('file.dateFile')
                ->getQuery()
                ->getArrayResult();
                
    
    
        }

I get the error:

[Syntax Error] line 0, col 245: Error: Unexpected ‘)’

Apparently it comes from the line with the CASE because if I delete this line, no more errors!
How to correctly formulate my request ?

Tags:

2

Answers


  1. 0

    The issue is related to the use of the CASE statement inside the addSelect() method in Symfony’s QueryBuilder. The addSelect() method does not handle complex expressions like CASE statements directly. Instead, you can use the select() method with the expr() function to handle complex expressions.

    public function findBlByOpeByFileByDate($idAppli, $dateDebut, $dateFin)
{
    return $this->createQueryBuilder('ligne')
        ->leftJoin('ligne.ecommCmdeps', 'cmd')
        ->innerJoin('ligne.numligne', 'tr')
        ->innerJoin('tr.idfile', 'file')
        ->innerJoin('file.idappli', 'app')
        ->innerJoin('tr.idStatut', 'statut')
        ->leftJoin('ligne.bls', 'bl')
        ->where('app.idappli IN (:id)')
        ->setParameter('id', $idAppli)
        ->andWhere('file.dateFile >= :dateDebut')
        ->setParameter('dateDebut', $dateDebut)
        ->andWhere('file.dateFile <= :dateFin')
        ->setParameter('dateFin', $dateFin)
        ->select('file.idfile')
        ->addSelect('file.dateFile')
        ->addSelect('file.filename')
        ->addSelect('COUNT(DISTINCT ligne.numligne) as nbCmde')
        ->addSelect($this->getCaseExpr('bl.siteProduction', 'ligne.numligne', 0) . ' as cmdProd')
        ->addSelect('MIN(bl.dateProduction) as minDateProd')
        ->addSelect('MAX(bl.dateProduction) as maxDateProd')
        ->addSelect('SUM(cmd.quantite) as nbArticles')
        ->addSelect('tr.dateDepot')
        ->groupBy('file.idfile')
        ->orderBy('file.dateFile')
        ->getQuery()
        ->getArrayResult();
}

private function getCaseExpr($condition, $column, $defaultValue)
{
    return 'COUNT(DISTINCT CASE WHEN ' . $condition . ' != ' . $defaultValue . ' THEN ' . $column . ' END)';
}

    In the code above, I’ve created a new private method called getCaseExpr() to generate the CASE statement. The method takes three arguments: the condition, the column to count, and a default value for the ELSE part. It will return a string representing the CASE statement. By using this method, you can encapsulate the complex expression and avoid the syntax error.

    Login or Signup to reply.
  2. 0

    You can try to add an ELSE and put your expression in double-quotes like this :

    ->addSelect("COUNT (DISTINCT CASE WHEN bl.siteProduction != 0 THEN ligne.numligne ELSE 'NULL' END) as cmdProd")
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search