How to update multiple fields using query builder in Doctrine2?

Question

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.

You are updating company entity, you have to update user entity. I guess you should use like : $qb->update('Application_Entity_User', 'u')->set('u.name', $post['name'])->set('u.mobile', $post['mobile'])->set('u.email', $post['email'])->where('u.userId', ':id'))->setParameter('id', $post['user_id'])->getQuery()->execute(); — herr
I have to update in company entity, updated fields are there in company but I have to update based on userId. It is in user entity. — user3668438
Can you please post your error message? I think c.userId may wrong here your relation field name which is given in entity will come. I have post previous comment based on this sentence : " I have to update the fields of user details in user entity using the userId." — herr

Marcin Necsord Szulc Marcin Necsord Szulc · Accepted Answer · 2016-01-29T15:30:29

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

How to update multiple fields using query builder in Doctrine2?

1 Answers