doctrine和symfony2中的多个更新查询 [英] Multiple update queries in doctrine and symfony2

问题描述

我已经尝试以多种方式执行此操作，但是我找不到它的正确语法。我的最新尝试是在（在Symfony2实体存储库类中）：

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')

这是外键在mysql表中，然后错误更改为找不到evaluate_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)

任何想法？

[更新]

下面的解决方案有一个问题。我也必须添加一个调用flush或者它将开始累积更新字段，如update answer set answerernr = 1，answerernr = 2 where id = 2。所以最终的解决方案是：

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

推荐答案

您正在为id设置变量，但不绑定它执行查询之前的值。

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

修正代码：

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

至于您的evaluation_id字段，请检查名称在实体类中，您需要在使用DQL编写查询时使用字段名称，而不是列名。

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.

这篇关于doctrine和symfony2中的多个更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！