问题描述:

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 ?

网友答案:

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.

网友答案:

You have to change this

->where("u.id IN (:ids)")->setParameter('deletedAt', null)

into

->where($qb->expr()->in('sc.id',$ids))

of course $ids has to be an array and not a string.

网友答案:

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

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

and after in you update query:

->setParameter('ids', $arrayOfIds)
网友答案:

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);  
相关阅读:
Top