symfony2 doctrine expr subquery: Error: Invalid parameter number

Question

trying to get liked statuses by user.

public function getLikedStatuses(User $user)
{
    $qb = $this->_em->createQueryBuilder();
                $qb
                ->select('s.id')
                ->from('WallBundle:Likes','l')
                ->innerJoin('l.status', 's')
                ->where('l.user = :user')
                ->setParameter('user', $user)
                ->orderBy('s.id','DESC')
            ;

    $qb2=  $this->_em->createQueryBuilder()
        ->select('st')
        ->from('WallBundle:Status','st');

       $qb2 ->andWhere($qb2->expr()->in('st.id',$qb->getDQL()));


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

Error: Invalid parameter number: number of bound variables does not match number of tokens

BTW: when i dump the $qb->getDQL():

string 'SELECT s.id FROM TB\WBundle\Entity\Likes l LEFT JOIN l.status s WHERE l.user = :user' (length=87)

BTW2: when i replace the '$qb->getDQL()' for (12073) (id of status) it works...

Answer 1

Actually you can probably do a simpler query, depending on how you did your annotations.

Something like :

$qb =  $this->_em->createQueryBuilder()
    ->select('s')
    ->from('WallBundle:Status','st')
    ->innerJoin('st.like','l')
    ->where('l.user = :user')
    ->setParameter('user', $user)
    ->getQuery()
    ->getResult();

This should do the same, is shorter and is easier to understand as there is only one query.

---

Update : I have had the exact same problem as you did today and resolved it by putting the two setParameters in the second query. And therefore I found another way to solve it!

I did something exactly like that :

$qb = $this->_em->createQueryBuilder()
    ->select('s.id')
    ->from('WallBundle:Likes','l')
    ->innerJoin('l.status', 's')
    ->where('l.user = :user')
    ->orderBy('s.id','DESC')
    ->getDQL()
;

$qb2=  $this->_em->createQueryBuilder()
    ->select('st')
    ->from('WallBundle:Status','st');
    ->where('st.like IN('.$qb.')')
    ->setParameter('user', $user)
    ->getQuery()
;