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...

Try doing the setParameter() on the second query or try doing getQuery() on $qd before putting it in the second one. Seems like doctrine thinks the parameters should be on the second query. — Hugo Dozois
– Hugo Dozois, Commented Apr 8, 2013 at 15:41
@squazic string 'SELECT st FROM WallBundle:Status st' (length=35). Hugo hm... how could i set it to second query? There are statuses and my needs are select first ids based on USER ID in LIKES. — Lukas Lukac
– Lukas Lukac, Commented Apr 8, 2013 at 15:49

Hugo Dozois · Accepted Answer · 2013-04-12 14:37:47Z

4

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()
;

edited Apr 12, 2013 at 14:37

answered Apr 8, 2013 at 15:54

Hugo Dozois

8,49812 gold badges56 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Lukas Lukac Over a year ago

looks very functional... big hope... but failed, getting still the same error.

Lukas Lukac Over a year ago

thx! The second query works... why it didn't came to mind me.. oh. Dont know why first but .. Ok that's nice and it will be work for 100% always right? Without bugs, because the inner join come from likes.status_id ... and because of foreign key there to statuses table it has to exists. So no problem right? About performances: It took 7.86ms which is also good right?

Hugo Dozois Over a year ago

@Trki If it works keep it. I mean 7.86 ms is kind of small compared on the time it takes for printing the view! It's probably not even significative.

Hugo Dozois Over a year ago

@Trki I don't think it could break in the future! With the situation you have it's better to make a single query with the join explicitly then doing bunch of unclear queries to gain a few ms. Always better to go with what's clear first than going with what's the most optimized. Because most of the time the bottle neck is not where we think it is!

Lukas Lukac Over a year ago

ofcourse i did, but now i get it... my mistake was at visited bad "user wall" so it was loading 870objects hh pretty nice 20ms for that number. Thx again, get it like solved and have a nice day.

|

j0k · Accepted Answer · 2013-04-08 15:36:58Z

0

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

edited Apr 8, 2013 at 15:36

j0k

22.8k28 gold badges81 silver badges90 bronze badges

answered Apr 8, 2013 at 11:19

Freenando

4058 silver badges16 bronze badges

Comments

DonCallisto · Accepted Answer · 2013-04-08 15:56:09Z

0

Try to change this

$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()));

answered Apr 8, 2013 at 15:56

DonCallisto

30k9 gold badges74 silver badges104 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 11:53:58Z

As doctrine doesn't support a limit when using subquery (look at my comment) one possible solution is to execute two separate queries which is not ideal but works.

/**
 * @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();
}

Collectives™ on Stack Overflow

symfony2 doctrine expr subquery: Error: Invalid parameter number

4 Answers 4

10 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

10 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related