如何在 Doctrine 查询生成器中进行多个 WHERE IN 列查询?

2022-01-16 00:00:00 php mysql symfony doctrine where-in

我想使用带有两列检查的 WHERE IN 语句更新 db 中的多条记录.

I would like to update multiple records in db using WHERE IN statement with two column check.

Pure MySql 原始查询看起来像这样.. 它可以工作:

Pure MySql raw query looks something like this.. and it works:

UPDATE poll_quota q SET q.count = q.count+1 WHERE q.form_id=14 AND ((q.field_id,q.value) IN (('A',1),('B',1)))

我的代码:

$this->createQueryBuilder("q")
            ->update()
            ->set("q.count","q.count+1")
            ->where("q.form_id=:form_id")
            ->andWhere("((q.field_id,q.value) IN (:wherein))")
            ->setParameter(":form_id",$form_id)
            ->setParameter(":wherein",$where_in)
            ->getQuery()
            ->execute()
        ;

输出:

[Syntax Error] line 0, col 103: Error: Expected DoctrineORMQueryLexer::T_CLOSE_PARENTHESIS, got ','

[1/2] QueryException: UPDATE EdgePollBundleEntityQuota q SET q.count = q.count+1 WHERE q.form_id=:form_id AND ((q.field_id,q.value) IN (:wherein))   +

尝试这样做，也不起作用:

Attemp to do sth like this, also doesn't work:

[...]->andWhere("((q.field_id,q.value) IN ({$where_in}))")

$where_in 包含这样的字符串:

$where_in contains string like this:

"('A',1),('B',1)"

推荐答案

DQL 不允许在 WHERE IN 语句中使用多个列，因为并非所有 DBMS 都支持它.您可以使用 $this->getEntityManager()->getConnection()->executeUpdate()

DQL doesn't allow using multiple columns in a WHERE IN statement since not all DBMS support it. You can run it with the raw SQL using $this->getEntityManager()->getConnection()->executeUpdate()

相关文章