How to make multiple WHERE IN column query in Doctrine query builder?

Question

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

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

My code:

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

Output:

[Syntax Error] line 0, col 103: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got ','

[1/2] QueryException: UPDATE Edge\PollBundle\Entity\Quota 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 contains string like this:

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

Could you not just use `->andWhere("q.field_id IN (:wherein)")->andWhere("q.value IN (:wherein)")`? — qooplmao, Jul 15 '15 at 21:24
Script updates only selected pairs of field_id and value. Separate IN statements won't work in this case. — Tomk, Jul 15 '15 at 23:19
I don't think I really understand what the script does/would do, unfortunately. — qooplmao, Jul 16 '15 at 05:27

score 5 · Accepted Answer · answered Jul 15 '15 at 16:43

5

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

answered Jul 15 '15 at 16:43

Kevin Sharp

521
4
6

1

I was afraid that's the issue. Thanks for help – Tomk Jul 15 '15 at 20:08

How to make multiple WHERE IN column query in Doctrine query builder?

My code:

Output:

1 Answers1

Linked