How to update a field in doctrine to set it null

Question

I want to set null to a field in doctrine and here is the sentence

$em = $this->getDoctrine()->getManager();
$qb = $em->createQueryBuilder();
$query = $qb->update('Model\Example', 'u')->set('u.deletedAt', ':deletedAt')
->where("u.id IN (:ids)")->setParameter('deletedAt', null)
->setParameter('ids', $ids)
->getQuery();
 $query->execute();

i think that this code should do the job, but im getting this exception

An exception occurred while executing 'UPDATE example SET deleted_at = ? WHERE (id IN (?)) AND (example.deleted_at IS NULL)' with params [null, "5,6"]: SQLSTATE[22P02]: Invalid text representation: 7 ERROR: la sintaxis de entrada no es válida para integer: «5,6»

first of all why doctrine is adding that AND (example.deleted_at IS NULL) am i doing something wrong ?

Answer 1

Your original query looks like it should work. I duplicated and tested with:

    $em = $this->getService('doctrine.orm.entity_manager');
    $qb = $em->createQueryBuilder();

    $qb->update('Cerad\Bundle\PersonBundle\Entity\Person','person');

    $qb->set('person.verified',':verified');
    $qb->setParameter('verified',null);

    $qb->where('person.id IN (:ids)');
    $qb->setParameter('ids',array(1,2,3));

    echo $qb->getQuery()->getSql(); // UPDATE persons SET verified = ? WHERE id IN (?)

    $qb->getQuery()->execute();

Works as expected.

Are you sure you copy/pasted your exact code? No editing after the fact? Verify your ids array really is an array of integers. That is the only spot I could see where there might be an issue. And do make sure your error is coming from the code you posted. Maybe something else is going on? Try isolating your code in a command object. And of course deletedAt has it's is nullable set to true?

There is no real need to use the expr object for this case. Doctrine 2 correctly handles arrays for IN statements.

====================================

I suspect you have $ids = '5,6'? Try setting it to: $ids = array(5,6); Though even with a string I don't see how it's messing up the query.