CODEWITHSUNDEEP
phpdoctrine-ormdoctrinequery-builder
user3668438
user3668438

Reputation: 165

How to update multiple fields using query builder in Doctrine2?

I have to update multiple fields, when I tried to update fields in other table using foreign key id using getRepository() and findOneById() getting bug as unrecognised field, so then later tried to implemented it using query builder. but query doesn't get executing getting bug like undefined fields.

This is the code I have tried:

$this->em
    ->getRepository('Application_Entity_Company', 'c')
    ->findOneBy(array('c.userId'=>$post['user_id']));

and

$qb->update('Application_Entity_Company', 'c')
    ->set('c.name', $post['name'])
    ->set('c.mobile', $post['mobile'])
    ->set('c.email', $post['email'])
    ->where($qb->expr()
    ->eq('c.userId', ':id'))
    ->setParameter('id', $post['user_id'])
    ->getQuery()
    ->execute();

Here userId is the foreign key. I have to update the fields of user details in user entity using the userId.

Upvotes: 2

Views: 8727

Answers (1)

Marcin Szulc
Marcin Szulc

Reputation: 1271

The problem is with not obvious usage of set method. Second parameter is expected to be an expression instead of the obvious user input.

For sample incorrectly used set code:

$queryBuilder = $entityManager->getRepository()->createQueryBuilder('u');
$queryBuilder->update()
    ->set('u.userFirstName', 'Michael')
    ->where('u.userId = :userId')
    ->setParameter('userId', 111)
    ->getQuery()
    ->execute();

SQL representation:

UPDATE user SET user_first_name = Michael WHERE user_id = 111;

You will get following error:

[Semantical Error] line 0, col 49 near 'Michael WHERE': Error: 'Michael' is not defined.

This is because your database assumes Michael is a table column name which for obvious reasons is not defined.

Solution is to either use \Doctrine\ORM\Query\Expr or by binding parameters:

$queryBuilder = $mapper->getRepository()->createQueryBuilder('u');
$queryBuilder->update()
    ->set('u.userFirstName', ':userFirstName')// Alternatively $queryBuilder->expr()->literal('Michael')
    ->where('u.userId = :userId')
    ->setParameter('userId', 111)
    ->setParameter('userFirstName', 'Michael')
    ->getQuery()
    ->execute();

Upvotes: 3

Related Questions