UniqueEntity with two (many) fields is not working

Question

I have a problem related to using Symphony (v5.4) with API Platform based on PHP 8.1.

I have an entity called Customer which has email, phoneNumber fields that are unique per tenantId (two fields per unique db index).

Part of the Doctrine mapping (XML) re. to these indexes:

<unique-constraints>
            <unique-constraint columns="tenant_id,email" name="unique_customer_email"/>
            <unique-constraint columns="tenant_id,phone_number" name="unique_customer_phone_number"/>
</unique-constraints>

Also, the entity has UniqueEntity annotation (use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;) like below

<?php

declare(strict_types=1);

namespace App\Modules\RentalCustomers\Application\Query;

use ApiPlatform\Core\Annotation\ApiFilter;
use ApiPlatform\Core\Annotation\ApiProperty;
use ApiPlatform\Core\Annotation\ApiResource;
use ApiPlatform\Core\Annotation\ApiSubresource;
use ApiPlatform\Core\Bridge\Doctrine\Orm\Filter\SearchFilter;
use App\Shared\Address\Address;
use App\Shared\Email\Email;
use App\Shared\Email\Serializer\EmailNormalizer;
use App\Shared\TaxNumber\TaxNumber;
use DateTimeImmutable;
use Doctrine\Common\Collections\ArrayCollection;
use Doctrine\Common\Collections\Collection;
use Doctrine\ORM\Mapping as ORM;
use libphonenumber\PhoneNumber;
use Misd\PhoneNumberBundle\Validator\Constraints\PhoneNumber as AssertPhoneNumber;
use Ramsey\Uuid\UuidInterface;
use Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntity;
use Symfony\Component\Serializer\Annotation\Context;
use Symfony\Component\Serializer\Annotation\Groups;
use Symfony\Component\Validator\Constraints as Assert;

#[ApiResource(
    collectionOperations: [
        'get' => [
            'security_get_denormalize' => "is_granted('CUSTOMER_VIEW', object)",
        ],
        'post' => [
            'security_post_denormalize' => "is_granted('CUSTOMER_CREATE', object)",
        ],
    ],
    itemOperations: [
        'get' => [
            'normalization_context' => [
                'groups' => [
                    'customer:read',
                    'with:address',
                ],
            ],
            'security' => "is_granted('CUSTOMER_VIEW', object)",
        ],
        'put' => [
            'normalization_context' => [
                'groups' => [
                    'customers:write',
                    'with:address',
                ],
            ],
            'security' => "is_granted('CUSTOMER_UPDATE', object)",
        ],
    ],
    attributes: [
        'validation_groups' => [self::class, 'validationGroups'],
    ],
    denormalizationContext: [
        'groups' => [
            'customers:write',
            'with:address',
        ],
    ],
    normalizationContext: [
        'groups' => [
            'customers:read',
            'with:address',
        ],
    ],
    routePrefix: '/rental-customers',
)]
#[UniqueEntity(['tenantId', 'email'], null, null, null, null, null, 'email')]
#[UniqueEntity(['tenantId', 'phoneNumber'], null, null, null, null, null, 'phoneNumber')]
#[ApiFilter(SearchFilter::class, properties: ['lastName' => 'partial', 'email' => 'partial', 'phoneNumber' => 'partial'])]
class Customer
{
    #[ApiProperty(writable: false, identifier: true)]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    public UuidInterface $id;

    #[ApiProperty(writable: true, required: true)]
    #[Assert\NotBlank(groups: ['company', 'Default'])]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    public ?string $companyName;

    #[Assert\NotBlank(groups: ['company', 'Default'])]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    #[ApiProperty(
        writable: true,
        required: true,
        attributes: [
            'openapi_context' => [
                'type' => 'string',
                'example' => '542453244242',
            ],
        ]
    )]
    public ?TaxNumber $companyTaxNumber;

    #[ApiProperty(required: true)]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    #[Assert\NotBlank(groups: ['person', 'Default'])]
    public string $firstName;

    #[ApiProperty(required: true)]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    #[Assert\NotBlank(groups: ['person', 'Default'])]
    public string $lastName;

    /**
     * @AssertPhoneNumber
     */
    #[ApiProperty(
        attributes: [
            'openapi_context' => [
                'type' => 'string',
                'example' => '+48500600700',
            ],
        ]
    )]
    #[Assert\NotBlank]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    public PhoneNumber $phoneNumber;

    #[ApiProperty(
        required: true,
        attributes: [
            'openapi_context' => [
                'type' => 'string',
                'example' => '[email protected]',
            ],
        ]
    )]
    #[Assert\Email, Assert\NotBlank]
    #[Context([EmailNormalizer::class])]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    public Email $email;

    #[Assert\NotNull(groups: ['person', 'Default']), Assert\NotBlank(groups: ['person', 'Default'])]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    public string $nationalIdentificationNumber;

    #[ApiSubresource]
    #[Groups(['customer:read', 'customers:read'])]
    public Collection $documents;

    #[Assert\Valid]
    #[Groups(['customer:read', 'customers:write', 'customers:read'])]
    #[ORM\Embedded(Address::class, 'address_')]
    public Address $address;

    #[ApiProperty(readable: false, writable: false)]
    public UuidInterface $tenantId;

    #[ApiProperty(writable: false)]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    public DateTimeImmutable $createdAt;

    #[Assert\NotBlank]
    #[Groups(['customer:read', 'customers:read', 'customers:write'])]
    #[ApiProperty(
        writable: true,
        required: true,
        attributes: [
            'openapi_context' => [
                'type' => 'string',
                'example' => 'person or company',
            ],
        ]
    )]
    public CustomerType $type;

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

    public function toArray(): array
    {
        return [
            'email' => $this->email->value(),
            'firstName' => $this->firstName,
            'lastName' => $this->lastName,
            'phoneNumber' => $this->phoneNumber->getRawInput(),
            'address' => $this->address->toArray(),
            'nationalIdentificationNumber' => $this->nationalIdentificationNumber,
            'type' => (string) $this->type,
            'companyName' => $this->companyName ?? '',
            'companyTaxNumber' => $this->companyTaxNumber !== null ? (string) $this->companyTaxNumber : '',
        ];
    }

    public static function validationGroups(self $customer): array
    {
        if ($customer->type->isCompany()) {
            return ['Default', 'company'];
        }

        return ['Default', 'person'];
    }
}

And... Symfony is not catching the constraint violation. It only works when I have an index on a single column, not for more (like here, for two). The endpoint is returning an exception (based on PostgreSQL) instead of 422 status code with a field-related conflict error:

Uncaught PHP Exception Doctrine\DBAL\Exception\UniqueConstraintViolationException: "An exception occurred while executing a query: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate
 key value violates unique constraint "unique_customer_email" DETAIL:  Key (tenant_id, email)=(99ca30b3-56e6-4177-87a1-f5bd6e956ea4, [email protected]) already exists."

Any ideas? I was googling this problem a few times in a row, and still I'm here...

Worth adding is that I have objects for thoese fields like: PhoneNumber, Email and TenantId, they are not scalars! BUT, for a single field it works, so I assume that's not the problem.

I was trying to use different order of these fields. I was also trying to specyfiy errorPath but that's something different... :(

Answer 1

Probably not a complete solution, but things that might be helpful to consider:

UniqueEntity and the doctrine unique constraints are different things that have nothing to do with each other.

The UniqueEntity is a validator, that relies on the entity repository trying to fetch all entities that match the criteria. If no entity matching the criteria is found (or only the one that is actually being validated) validation will be passed, if a different entity with the given criteria is actually found, the validation will fail. From the docs:

repositoryMethod: The name of the repository method used to determine the uniqueness. If it's left blank, findBy() will be used. The method receives as its argument a fieldName => value associative array (where fieldName is each of the fields configured in the fields option). https://symfony.com/doc/current/reference/constraints/UniqueEntity.html

The unique constraint on the other hand is creating a real unique index in the database table. That's why you get the exception you mentioned.

I'd suggest you just step into

Symfony\Bridge\Doctrine\Validator\Constraints\UniqueEntityValidator::validate($entity, Constraint $constraint)

espacially line 137:

$result = $repository->{$constraint->repositoryMethod}($criteria)

to see why the repository is not able to fetch the conflicting entity.