Doctrine always making update query for custom Money type, even if value does not change

[bogdan-dubyk]

I have custom Money type:

class MoneyType extends Type
{
    public const TYPE = 'money';

    public function getName(): string
    {
        return self::TYPE;
    }

    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        return $platform->getIntegerTypeDeclarationSQL($column);
    }

    /**
     * @param mixed $value
     * @param AbstractPlatform $platform
     *
     * @return MoneyInterface
     *
     * @throws Exception
     */
    public function convertToPHPValue(mixed $value, AbstractPlatform $platform): MoneyInterface
    {
        if (!is_int($value)) {
            throw new Exception(
                sprintf(
                    'Could not convert %s to Money. Value should be of type integer.',
                    $value
                )
            );
        }

        if ($value < 0) {
            throw new Exception(
                sprintf(
                    'Could not convert %s to Money. Value should be positive number.',
                    $value
                )
            );
        }

        return new Money($value, Money::CURRENCY_USD);
    }

    /**
     * @param mixed $value
     * @param AbstractPlatform $platform
     *
     * @return int
     *
     * @throws Exception
     */
    public function convertToDatabaseValue(mixed $value, AbstractPlatform $platform): int
    {
        if (!$value instanceof MoneyInterface) {
            throw new Exception(
                'Invalid value passed as a Money to %s. Value should instance of %s',
                get_class($this),
                MoneyInterface::class
            );
        }

        return $value->getAmount();
    }

    /**
     * {@inheritdoc}
     */
    public function requiresSQLCommentHint(AbstractPlatform $platform)
    {
        return true;
    }
}

and in entity it looks like this:

    /**
     * @ORM\Column(type="money")
     */
    public MoneyInterface $maxPrice;

and each time I'm making persist/flush doctrine sending update query even if value is the same

$entity = $this->em->getRepository(Entity::class)->find($id);
$this->em->persist($entity);
$this->em->flush()

leads to such queries

SELECT t0.id AS id_1, t0.max_price AS max_price_2 FROM table t0 WHERE t0.id = ?
"START TRANSACTION"
UPDATE table SET max_price = ? WHERE id = ?
"COMMIT"

How can avoid it??? Thank you a lot!!!

[Qvinticom]

For Ramsey\Uuid\Doctrine\UuidType and custom UuisType same things. Doctrine update uuid for all selected entities with relations

"START TRANSACTION" [] []

UPDATE part SET id = ? WHERE id = ? ["9d81e374-0258-11ec-a8f2-fa [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"9d81e374-0258-11ec-a8f2-fa163e37a975\")"] []

UPDATE part_session SET id = ?, date_end = ?, status = ? WHERE id = ? ["37df5300-1c9a-11ec-b910-04 [...]","[object] (DateTimeImmutable: {\"date\":\"2021-09-23 18:28:55.563340\",\"timezone_type\":3,\"timezone\":\"UTC\"})","failed","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"37df5300-1c9a-11ec-b910-04922658d9ec\")"] []

UPDATE part_session SET id = ? WHERE id = ? ["0569576c-1c9a-11ec-b910-04 [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"0569576c-1c9a-11ec-b910-04922658d9ec\")"] []

UPDATE part_session SET id = ? WHERE id = ? ["488fc9f1-1c99-11ec-b910-04 [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"488fc9f1-1c99-11ec-b910-04922658d9ec\")"] []

UPDATE part_session SET id = ? WHERE id = ? ["44c391f1-1c99-11ec-b910-04 [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"44c391f1-1c99-11ec-b910-04922658d9ec\")"] []

UPDATE part_session SET id = ? WHERE id = ? ["dd2e0ac6-1c97-11ec-b910-04 [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"dd2e0ac6-1c97-11ec-b910-04922658d9ec\")"] []

UPDATE part_session SET id = ? WHERE id = ? ["ce04b2a2-1c97-11ec-b910-04 [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"ce04b2a2-1c97-11ec-b910-04922658d9ec\")"] []

UPDATE part_session SET id = ? WHERE id = ? ["c205017f-1c97-11ec-b910-04 [...]","[object] (Ramsey\\Uuid\\Lazy\\LazyUuidFromString: \"c205017f-1c97-11ec-b910-04922658d9ec\")"] []

"COMMIT" [] []

This is a problem with many requests! Update set deadlock to table

[Qvinticom]

I have custom Money type:

class MoneyType extends Type
{
    public const TYPE = 'money';

    public function getName(): string
    {
        return self::TYPE;
    }

    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        return $platform->getIntegerTypeDeclarationSQL($column);
    }

    /**
     * @param mixed $value
     * @param AbstractPlatform $platform
     *
     * @return MoneyInterface
     *
     * @throws Exception
     */
    public function convertToPHPValue(mixed $value, AbstractPlatform $platform): MoneyInterface
    {
        if (!is_int($value)) {
            throw new Exception(
                sprintf(
                    'Could not convert %s to Money. Value should be of type integer.',
                    $value
                )
            );
        }

        if ($value < 0) {
            throw new Exception(
                sprintf(
                    'Could not convert %s to Money. Value should be positive number.',
                    $value
                )
            );
        }

        return new Money($value, Money::CURRENCY_USD);
    }

    /**
     * @param mixed $value
     * @param AbstractPlatform $platform
     *
     * @return int
     *
     * @throws Exception
     */
    public function convertToDatabaseValue(mixed $value, AbstractPlatform $platform): int
    {
        if (!$value instanceof MoneyInterface) {
            throw new Exception(
                'Invalid value passed as a Money to %s. Value should instance of %s',
                get_class($this),
                MoneyInterface::class
            );
        }

        return $value->getAmount();
    }

    /**
     * {@inheritdoc}
     */
    public function requiresSQLCommentHint(AbstractPlatform $platform)
    {
        return true;
    }
}

and in entity it looks like this:

    /**
     * @ORM\Column(type="money")
     */
    public MoneyInterface $maxPrice;

and each time I'm making persist/flush doctrine sending update query even if value is the same

$entity = $this->em->getRepository(Entity::class)->find($id);
$this->em->persist($entity);
$this->em->flush()

leads to such queries

SELECT t0.id AS id_1, t0.max_price AS max_price_2 FROM table t0 WHERE t0.id = ?
"START TRANSACTION"
UPDATE table SET max_price = ? WHERE id = ?
"COMMIT"

How can avoid it??? Thank you a lot!!!

I fix updates for Ramsey Uuid. I create custom type with same return value in convertToPHPValue and convertToDatabaseValue

[Fedik]

It is kind of expected behavior, because there is a strict comparison. There already an issue about it, somewhere.

Hint: Do not call $object->setFoobar($fooBarObject); if $fooBarObject did not changed for exiting Object instance.

[seyedmr]

i have same problem...

    public static function listen(Event $event): void
    {
        $mr = app()->getContainer()->get(ManagerRegistry::class);
        $em = $mr->getManager();
        $repo = $em->getRepository(Line::class);
        /** @var Line[] $lines */
        $lines = $repo->getLinesById([
            1154,
            1155,
            1000,
            2354,
            2571,
            2678,
            3043,
        ]);
        $em->clear();
        foreach ($lines as $line) {
            $user_line = new UserLine();
            $user_line->setNumber($line);
            $user_line->setAssignTime(time());
            $user_line->setGlobal(0);
            $user_line->setSendAvailable(true);
            $user_line->setGetAvailable(false);
            $user_line->setAutoAdminConfirm(false);
            $user_line->setUser($event->getUser());
            $user_line->setIssuer($event->getUser()->getParent());
            $em->persist($user_line);
        }
        $em->flush();
    }

in this code, some update happen! (per insert / one update query!)

[dimadeush]

@seyedmr it seems issue with primary key and your entity. F.E. if you are using Postgres uuid type and inside entity you have property string, without proper type mapping can lead to update query after every update/insert.

Maybe you need to map types and it will solve your problem:
doctrine: dbal: types: uuid: 'Ramsey\Uuid\Doctrine\UuidType'