Expr subquery in symfony2: error: invalid parameter number

Question

trying to get the statuses that the user likes.

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: the number of associated variables does not match the number of tokens

BTW: when I unload $ 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 '$ qb-> getDQL ()' for (12073) (status id), it works ...

Answer 1

Since the doctrine does not support the restriction when using a subquery ( see my comment ), one of the possible solutions is to fulfill two separate queries, which are not ideal but work.

 /** * @param User $user * @param int $limit * @param int $offset * @return User[] */ public function getUserFollowers(User $user, $limit = 20, $offset = 0) { $_followersIds = $this->getEntityManager() ->createQueryBuilder() ->select('IDENTITY(r.followeduser)') ->from($this->getEntityName(), 'r') ->where('r.followeeuser = :user') ->andWhere('r.followeduser !=:user') ->setParameter('user', $user) ->orderBy('r.id', 'DESC') ->setFirstResult($offset) ->setMaxResults($limit) ->getQuery() ->getResult(); $_usersQb = $this->getEntityManager()->createQueryBuilder(); $_usersQb ->select('u') ->from('UserBundle:User', 'u') ->where('u.id IN (:followersIds)') ->setParameter('followersIds', array_values($_followersIds)); return $_usersQb->getQuery()->getResult(); } 

Answer 2

In fact, you can probably make a simpler request, depending on how you made your annotations.

Sort of:

 $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, shorter and easier to understand, since there is only one request.

---

Update: I had the same problem as today and resolved it by putting two setParameters in the second request. And so I found another way to solve it!

I did something 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() ; 

Answer 3

Try replacing ->where('l.user = :user') for this where('l.user = ?1') and add $qb->setParameter(1, $yourValue);

Answer 4

Try to change it.

 $qb2= $this->_em->createQueryBuilder() ->select('st') ->from('WallBundle:Status','st'); $qb2 ->andWhere($qb2->expr()->in('st.id',$qb->getDQL())); 

to

  $qb2= $this->_em->createQueryBuilder() ->select('st') ->from('WallBundle:Status','st'); ->where($qb2->expr()->in('st.id',$qb->getDQL()));