Multiple update queries in doctrine and symfony2

Question

I've tried to do this in several ways but i can't find the exact syntax for it to work. My latest attempt is below (in a Symfony2 Entity Repository class):

public function updateOrdering($new_order, $evaluation_id)
{
    $qb = $this->createQueryBuilder('IddpRplusBundle:Answer');
    foreach($new_order as $item){
        $id = $item['id'];
        $answernr = $item['answernr'];
        $q = $qb->update('IddpRplusBundle:Answer a')
                ->set('a.answernr', $answernr)
                ->where('a.id = :id')
                ->getQuery()
                ->execute()
                ;
    }
}

The error is " Invalid parameter number: number of bound variables does not match number of tokens".. And I would also like to add a second where clause

->where('a.evaluation_id = :evaluation_id')

which is a foreign key in the mysql table but then the error changes to "can't find the evaluation_id field" even though it exists in the table itself (the relationship between evaluation and answer entities is one to many and it's mapped as such in the entities as well)

Any ideas?

[UPDATE]

There was a gotcha in the solution below. I also had to add a call to flush or it would start accumulating the update field like this "update answer set answernr=1, answernr=2 where id=2". So the final solution was:

->set('a.answernr', (int)$answernr + (int)$start)
->where('a.id = :id AND a.evaluation = :evaluation_id ')
                ->setParameters(array('id' => $id,'evaluation_id' => $evaluation_id))
                ->getQuery()
                ->execute()
                ;
        $this->_em->flush();

Answer 1

You are setting a variable for id, but not binding it to a value before you execute the query.

Fixed code:

public function updateOrdering($new_order, $evaluation_id)
{
    $qb = $this->createQueryBuilder('IddpRplusBundle:Answer');
    foreach($new_order as $item){
        $id = $item['id'];
        $answernr = $item['answernr'];
        $q = $qb->update('IddpRplusBundle:Answer a')
            ->set('a.answernr', $answernr)
            ->where('a.id = :id')
            ->setParameter('id', $id)
            ->getQuery()
            ->execute();
    }
}

As for your "evaluation_id" field, check the name in the entity class, you need to use the field name when composing queries in DQL, not column names.