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 ?

Solution 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.

Solution 2

When you set the value with PHP null script , it's not understood for doctrine because when transforming to the native sql ,he will not replace null with null value as string , so to resolve , pass the null value as string like

$qb->set('q.deletedAt','NULL');

Solution 3

thanks @Cerad the problem was that i was using the soft-delete extension from StofDoctrineExtensionsBundle and the bundle was adding the soft delete filter, so i just disabled the soft delete filter and now it works as expected,posting the solution many thanks.

$em = $this->getDoctrine()->getManager();
$em->getFilters()->disable('softdeleteable'); // this was the problem when you use the soft delete extension you need to disable the filter if you want to reactivate deleted records
$qb = $em->createQueryBuilder();
$qb->update('Model\Example', 'q');
$qb->set('q.deletedAt',':deletedAt');
$qb->setParameter('deletedAt',null);
$qb->where("q.id IN (:ids)");
$qb->setParameter('ids', $ids);