CODEWITHSUNDEEP
phpsymfonydoctrine-ormdoctrinequery-builder
metalvarez
metalvarez

Reputation: 616

How to update a field in doctrine to set it null

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 ?

Upvotes: 10

Views: 20900

Answers (4)

Yassine CHABLI
Yassine CHABLI

Reputation: 3724

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

Upvotes: 12

metalvarez
metalvarez

Reputation: 616

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

Upvotes: 2

Cerad
Cerad

Reputation: 48865

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.

Upvotes: 9

Amine
Amine

Reputation: 271

the problem is that you $ids is a string. you can make:

$arrayOfIds = explode(",", $ids);

and after in you update query:

->setParameter('ids', $arrayOfIds)

Upvotes: 0

Related Questions