Doctrine 2: Update query with query builder

Question

Hi I've got the following query but it doesn't seem to work.

$q = $this->em->createQueryBuilder()     ->update('models\User', 'u')     ->set('u.username', $username)     ->set('u.email', $email)     ->where('u.id = ?1')     ->setParameter(1, $editId)     ->getQuery(); $p = $q->execute(); 

This returns the following error message:

Fatal error: Uncaught exception 'Doctrine\ORM\Query\QueryException' with message '[Semantical Error] line 0, col 38 near 'testusername WHERE': Error: 'testusername' is not defined.' in ...

I would be glad of any help

Answer 1

I think you need to use ->set() It's much safer to make all your values parameters:

$queryBuilder = $this->em->createQueryBuilder(); $query = $queryBuilder->update('models\User', 'u')         ->set('u.username', ':userName')         ->set('u.email', ':email')         ->where('u.id = :editId')         ->setParameter('userName', $userName)         ->setParameter('email', $email)         ->setParameter('editId', $editId)         ->getQuery(); $result = $query->execute(); 

Answer 2

Let's say there is an administrator dashboard where users are listed with their id printed as a data attribute so it can be retrieved at some point via JavaScript.

An update could be executed this way …

class UserRepository extends \Doctrine\ORM\EntityRepository {     public function updateUserStatus($userId, $newStatus)     {         return $this->createQueryBuilder('u')             ->update()             ->set('u.isActive', '?1')             ->setParameter(1, $qb->expr()->literal($newStatus))             ->where('u.id = ?2')             ->setParameter(2, $qb->expr()->literal($userId))             ->getQuery()             ->getSingleScalarResult()         ;     } 

AJAX action handling:

# Post datas may be: # handled with a specific custom formType — OR — retrieved from request object $userId = (int)$request->request->get('userId'); $newStatus = (int)$request->request->get('newStatus'); $em = $this->getDoctrine()->getManager(); $r = $em->getRepository('NAMESPACE\User')         ->updateUserStatus($userId, $newStatus); if ( !empty($r) ){     # Row updated } 

Working example using Doctrine 2.5 (on top of Symfony3).