Doctrine Criteria expression value : static values not seen, QueryException awaiting for parameters

I’m using Doctrine Criteria to avoid repeating some filtering business logic. Since my comparison values are static, I first passed them directly in the ->expr() conditions :

    /**
     * @return DoctrineCommonCollectionsCriteria
     */
    public function lastContratCriteria()
    {
        $criteria = Criteria::create();

        $baseCondition = $criteria->expr()->andX(
          $criteria->expr()->eq('BaseType', 1), // STATIC VALUE 
          $criteria->expr()->orX(
            $criteria->expr()->gt('dateFinContractuellePrevue','CURRENT_DATE()'), // STATIC VALUE 
            $criteria->expr()->isNull('dateFinContractuellePrevue')
          )
        );

        $criteria
          ->andWhere($baseCondition)
          ->orderBy(['dateDebut' => 'DESC'])
          ->setMaxResults(1);

        return $criteria;
    }

But this returned “Too few parameters: the query defines 2 parameters but you bound 0“.

With that DQL output :
WHERE contrat.BaseType = :BaseType AND (contrat.dateFinContractuellePrevue > :dateFinContractuellePrevue OR contrat.dateFinContractuellePrevue IS NULL).

So I changed the static values for bound parameters :

     $baseCondition = $criteria->expr()->andX(
          $criteria->expr()->eq('BaseType', ':bType'),
          $criteria->expr()->orX(
            $criteria->expr()->gt('dateFinContractuellePrevue',':dateFinCP'),
            $criteria->expr()->isNull('dateFinContractuellePrevue')
          )
     );

This worked then, but No matter how I named the parameters in Criteria, I had to call each setParameter() with its correspondent field name, as following !!

    /**
     * @return DoctrineORMQueryBuilder
     * @throws DoctrineORMQueryQueryException
     */
    public function getCurrentContratQB()
    {
        return $this->createQueryBuilder('contrat')
              ->addCriteria($this->lastContratCriteria());
    }

    /**
     * @return DoctrineCommonCollectionsCollection
     * @throws DoctrineORMQueryQueryException
     */
    public function getSeminaire()
    {
        $qb = $this->createQueryBuilder('salarie')
          ->andWhere('salarie.archive = FALSE')

          ->leftJoin('salarie.contrats', 'c')
          ->addSelect('c') 

          // Criteria is called here
          ->andWhere("c.id IN (". $this->contratRepository->getCurrentContratQB() .")")

          //  ONLY ORIGINAL FIELD NAME IS RECOGNIZED HERE, not the bound parameter name
          ->setParameter('BaseType', 1) // Should be bType, not BaseType
          ->setParameter('dateFinContractuellePrevue', 'CURRENT_DATE()') // Should be :dateFinCP

        //dump($qb->getDQL());

        return $qb->getQuery()
          ->getResult();
    }

This sounds weird. Am I doing something wrong ? Thanks !!

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *