DQL You can't specify target table 'user_addresses' for update in FROM clause

Question

I am trying to do a Doctrine 2 DQL update with a join. I have read that you cannot do this directly but have to use a sub-query, here's my code so far.

public function unsetNonDefaultBillingAddresses($userId, $userAddressId)
{
    $builder1 = $this->getEntityManager()->createQueryBuilder();
    $subQuery = $builder1->select('a1.userAddressId')
            ->from('Application\Entity\UserAddresses', 'a1')
            ->leftJoin('a1.user', 'u')
            ->where('u.userId = ?1');

    $builder2 = $this->getEntityManager()->createQueryBuilder();
    $builder2->update('Application\Entity\UserAddresses', 'a2')
            ->set('a2.defaultBillingAddress', 0)
            ->where($builder2->expr()->in('a2.userAddressId', $subQuery->getDQL()))
            ->andWhere('a2.userAddressId != ?2')
            ->setParameter(1, $userId)
            ->setParameter(2, $userAddressId);

    \Freedom\Logger\InfoLogger::vardump($builder2->getDQL());
    return $builder2->getQuery()->execute();
}

I am trying to update the UserAddress table for a particular userId from the Users table. Here's my join code.

Users table:

/**
 * @var \Doctrine\ORM\PersistentCollection
 *
 * @ORM\OneToMany(targetEntity="Application\Entity\UserAddresses", cascade={"persist", "persist"}, mappedBy="user")
 */
private $addresses;

and in my UserAddreses table:

/**
 * @var \Application\Entity\Users
 *
 * @ORM\ManyToOne(targetEntity="Application\Entity\Users", inversedBy="addresses")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="user_id", referencedColumnName="user_id")
 * })
 */
private $user;

Here's the resulting DQL:

UPDATE Application\Entity\UserAddresses a2
SET a2.defaultBillingAddress = 0
WHERE a2.userAddressId IN
(
    SELECT a1.userAddressId
    FROM Application\Entity\UserAddresses a1
    LEFT JOIN a1.user u
    WHERE u.userId = ?1
) AND a2.userAddressId != ?2

I have seen in other posts that you have to do a sub-select but I don't know how to do this in the query builder.

Many thanks in advance.

I don't know how to do it the query builder either, but you should begin with a correct and working MySQL query and then try to code it into the builder afterwards. — Tim Biegeleisen, Apr 30 '16 at 07:32

score 0 · Accepted Answer · edited May 23 '17 at 11:52

I finally figured out how to do it in DQL thanks to https://stackoverflow.com/a/15297991/655741

I here's my final code.

public function unsetNonDefaultBillingAddresses($userId, $userAddressId)
{
    $builder = $this->getEntityManager()->createQueryBuilder();
    $builder->update('Application\Entity\UserAddresses', 'a')
            ->set('a.defaultBillingAddress', 0)
            ->where('a.user = ?1')
            ->setParameter(1, $userId)
            ->andWhere('a.userAddressId != ?2')
            ->setParameter(2, $userAddressId);

    return $builder->getQuery()->execute();
}

a.user is the join to the users entity, by passing it the userId Doctrine done the rest.

DQL You can't specify target table 'user_addresses' for update in FROM clause

1 Answers1