PHP Symfony DQL - Too few parameters: the query defines 1 parameters but you only bound 0

Question

i am trying to put together a SELECT in symfony repository project but for some reason getting error message in the subject. Been working on it for a few days but cannot find a solution with different variations of the code. This is the code

$qb3 = $this->getEntityManager()->createQueryBuilder();
$qb3-> select('check.userid')
    -> from('App\Entity\YY_Table', 'check')
    -> where('DATE_FORMAT(now(), \'%e-%b-%Y\') - DATE_FORMAT(check.datecreated, \'%e-%b-%Y\') <=360');

$qb2 = $this->getEntityManager()->createQueryBuilder();
$qb2-> select('((:numemails1 / COUNT(subs.id)) * 10)')
    -> from('App\Entity\XX_Table', 'subs')
    -> where($qb2->expr()->notIn('subs.id', $qb3->getDQL()))
    -> setParameter('numemails1', $numemails);
$rand_num = $qb2->getQuery()->getResult();

$qb1 = $this->getEntityManager()->createQueryBuilder();
$qb1-> select('subss.id')
    -> from('App\Entity\XX_Table', 'subss')
    -> where('RAND() < :sqbresult')
    -> orderBy('RAND()')
    -> setMaxResults($numemails)
    -> setParameter('sqbresult', $rand_num);

/*primary query to select users for future campaigns*/
$qb = $this->getEntityManager()->createQueryBuilder();
$qb -> select('s')
    -> from('App\Entity\XX_Table', 's')
    -> where($qb->expr()->In('s.id', $qb1->getDQL()));
    //-> where($expr->not($expr->exists($qb1->getDQL())));
return $qb ->getQuery() ->getResult();

and I am trying to get the alternative of .sql code below to select random entities from DB that passed some basic criteria

SELECT 
g.* FROM XX_table g
JOIN
    (SELECT 
         id
     FROM
         XX_table
     WHERE
         RAND() < (SELECT 
                    ((60000 / COUNT(*)) * 10) as rand_num
                   FROM
                    XX_table
                   WHERE 
                    id NOT IN (SELECT userID as id FROM YY_table emst WHERE CURDATE() - emst.datecreated <=360)
                  )
     ORDER BY RAND()
     LIMIT 60000) AS z ON z.id= g.id

I have checked answers on here: Too few parameters: the query defines 1 parameters but you only bound 0 and here https://github.com/stwe/DatatablesBundle/issues/685 and feel like solution is somewhere close but cannot get to it

Answer 1

The short answer is that you need to actually call:

$qb->setParameter('sqbresult', $rand_num)

before the last line. In fact, there is no reason to call it on $qb1, as this will basically get discarded.

The reason for this is that, in your code above, you are basically just using $qb1 as a DQL generation mechanism. Any parameters set on this are not passed on when the call to getDQL is made, only the string value of the DQL at that point.

If you were to var_dump($qb->getDQL()) just before the end, you would see something like:

SELECT s FROM App\Entity\XX_Table s WHERE s.id IN (
         SELECT subss.id FROM App\Entity\XX_Table subss
                         WHERE RAND() < :sqbresult
                         ORDER BY RAND() ASC
)

showing that :sqbresult still remains in the DQL and thus needs to have a parameter set.