Issue with doctrine UUID v4 conversion in symfony app

Question

Stack overflow community,

I'm facing an issue with UUID version 4 handling in my Symfony application when using Doctrine to fetch related entities from the database. Despite configuring my entities to use UUIDs as identifiers and ensuring my database supports it, I keep running into conversion problems.

Problem description:

After transitioning from using string identifiers to UUIDs (v4) for entity IDs, I've encountered a persistent problem where Doctrine seems to mishandle the UUID conversion, specifically when fetching related entities. The error manifests as Doctrine generating a query with UUIDs in a binary-like format (e.g., 0x546970E77A10401388351FA21F92E31A), which doesn't match any records due to format mismatch.

The most notable impact is when attempting to use the getEmailsTo() method on my EmailFormType entity to retrieve associated EmailTo entities. The method returns null values, indicating no records are found, presumably due to the UUID format issue.

Relevant code snippets:

1. EmailFormType entity (simplified):

   #[ORM\Entity(repositoryClass: EmailFormTypeRepository::class)]
   #[ORM\Table(name: 'WORKSPACE_EMAIL_FORM_TYPE')]
   class EmailFormType {
       #[ORM\Id]
       #[ORM\Column(name:"id", type: UuidType::NAME, unique: true, nullable: false)]
       #[ORM\GeneratedValue(strategy: 'CUSTOM')]
       #[ORM\CustomIdGenerator(class: 'doctrine.uuid_generator')]
       private Uuid $id;
       // Other properties and methods...
       /**
         * @var Collection
       */
       #[ORM\OneToMany(mappedBy: 'emailFormType', targetEntity: EmailTo::class)]
       private Collection $emailsTo;
        /**
         *
         */
         public function __construct()
         {
            $this->emailsTo = new ArrayCollection();
         }
       public function getEmailsTo(): Collection
       {
        return $this->emailsTo;
       }
   }
   

2. Function to fetch emails:

   public function getFormattedEmails(EmailFormType $emailFormType, string $type): string {
       return implode(';', $emailFormType->getEmailsTo()
           ->filter(fn(EmailTo $emailTo) => $emailTo->getType() === $type)
           ->map(fn(EmailTo $emailTo) => $emailTo->getEmail())
           ->toArray());
   }
   

My field in my ORACLE database :

ID --- VARCHAR2(100)

Executed request:

SELECT t0.id AS ID_1, t0.email AS EMAIL_2, t0.type AS TYPE_3, t0.email_form_type_id AS EMAIL_FORM_TYPE_ID_4 FROM WORKSPACE_EMAIL_TO t0 WHERE t0.email_form_type_id = 0x546970E77A10401388351FA21F92E31A;

Any help or guidance on this matter would be greatly appreciated!

Answer 1

I encountered an issue while trying to maintain a VARCHAR2(100) type for my entity ID field in the ORACLE database, and here's how I resolved it while ensuring proper handling of UUIDs:

1. Creation of a Custom Type: I created a custom field type to handle UUIDs.
2. Configuration Update: I added this custom type to my doctrine.yaml configuration.
3. Entity Modification: I updated my entity to utilize this custom field type.

Here's the class representing my custom field type:

<?php

namespace App\Doctrine;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;
use Symfony\Component\Uid\Uuid;

class CustomUuidType extends Type
{
    public function convertToDatabaseValue($value, AbstractPlatform $platform): mixed
    {
        return $value instanceof Uuid ? $value->toRfc4122() : $value;
    }

    // Ensure this method can return null for nullable fields
    public function convertToPHPValue($value, AbstractPlatform $platform): ?Uuid
    {
        return $value === null ? null : Uuid::fromString($value);
    }

    public function getName(): string
    {
        return 'custom_uuid';
    }

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

Modification to the doctrine.yaml file:

doctrine:
    dbal:
        types:
            custom_uuid: App\Doctrine\CustomUuidType

My entity setup:

#[ORM\Entity(repositoryClass: EmailFormTypeRepository::class)]
#[ORM\Table(name: 'WORKSPACE_EMAIL_FORM_TYPE')]
class EmailFormType
{
    #[ORM\Id]
    #[ORM\Column(name:"id", type:"custom_uuid", length: 100, unique: true, nullable: false)]
    #[ORM\GeneratedValue(strategy: 'CUSTOM')]
    #[ORM\CustomIdGenerator(class: 'doctrine.uuid_generator')]
    private Uuid $id;

    #[ORM\OneToMany(mappedBy: 'emailFormType', targetEntity: EmailTo::class)]
    private Collection $emailsTo;

    public function __construct()
    {
        $this->emailsTo = new ArrayCollection();
    }

    public function getId(): Uuid
    {
        return $this->id;
    }

    public function getEmailsTo(): Collection
    {
        return $this->emailsTo;
    }
}

With these changes, Doctrine now executes queries correctly, with proper conversion handling for UUIDs:

SELECT t0.id AS ID_1, t0.email AS EMAIL_2, t0.type AS TYPE_3, t0.email_form_type_id AS EMAIL_FORM_TYPE_ID_4 FROM WORKSPACE_EMAIL_TO t0 WHERE t0.email_form_type_id = ?
Parameters:
[
  "546970e7-7a10-4013-8835-1fa21f92e31a"
]

This setup ensures that my application correctly handles UUIDs as VARCHAR2(100) in the database while leveraging Symfony's powerful Doctrine ORM capabilities.