CODEWITHSUNDEEP
phpmysqlsqlsymfonydoctrine-orm
Alexander Schranz
Alexander Schranz

Reputation: 2458

Doctrine update query with equal operator as set

I have problems to set a value based on a statement

I want to activate a specific uc and deactivate all other ucs.

(uc.collection = :collectionId) should return true or false so it is correctly handled.

QueryBuilder:

    $queryBuilder = $this->getEntityManager()->createQueryBuilder();
    $queryBuilder->update('TestBundle:UserCollection', 'uc')
        ->set('uc.active', '(uc.collection = :collectionId)')
        ->where('uc.user = :userId');

    $queryBuilder->setParameter('collectionId', $collectionId);
    $queryBuilder->setParameter('userId', $userId);

Output DQL:

UPDATE TestBundle:UserCollection uc 
SET uc.active = (uc.collection = :collectionId) WHERE uc.user = :userId

getSQL Error:

[Syntax Error] line 0, col 81: Error: Expected 
Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got '='

Here the SQL I want:

UPDATE user_collections AS uc SET uc.active = (uc.idCollections = 1)
WHERE uc.idUsers = 74

Upvotes: 3

Views: 1444

Answers (1)

Jovan Perovic
Jovan Perovic

Reputation: 20201

Try setting it up via expr()->eq(...) expression:

$queryBuilder = $this->getEntityManager()->createQueryBuilder();
$queryBuilder->update('TestBundle:UserCollection', 'uc')
    ->set('uc.active', $queryBuilder->expr()->eq('uc.collection', ':collectionId'))
    ->where('uc.user = :userId');

$queryBuilder->setParameter('collectionId', $collectionId);
$queryBuilder->setParameter('userId', $userId);

Upvotes: 1

Related Questions