Symfony2 UniqueEntity multiple fields: false positive validation?

Question

I'm trying to validate uniqueness of an entity submitted from a form by using UniqueEntity Validation Constraint on multiple fields.

Code of the entity that should be unique has two fields - fieldA and fieldB, both unique:

/**
 * @ORM\Table(name="mytable")
 * @ORM\Entity
 * @DoctrineAssert\UniqueEntity(fields = {"fieldA", "fieldB"})
 */
class myClass
{
  /**
   * @ORM\Column(name="fieldA", type="string", length=128, unique=true)
   */
  protected $fieldA;

  /**
   * @ORM\Column(name="fieldB", type="string", length=128, unique=true)
   */
  protected $fieldB;
}

Suppose I already have a record in the database with values:

• fieldA = 'value_a', fieldB = 'value_b'

Now when I try to submit another one with values (fieldA = 'value_a', fieldB = 'value_c') from a form, Symfony2 generates a query to check uniqueness:

SELECT ... FROM ... WHERE fieldA = ? AND fieldB = ? ('value_a', 'value_c')

And the validation passes, because the result is an empty set, but I would expect it to fail, because fieldA won't be unique in this case. (The SQL insert fails with an duplicate entry error on 'value_a'.)

Symfony2's UniqueEntity documentation says:

This required option is the field (or list of fields) on which this entity should be unique. For example, you could specify that both the email and name fields in the User example above should be unique.

I think it confirms my expectations.

I found out in the source of UniqueEntityValidator (line 94), that the validator takes the fields as an array, and uses the "findBy" magic finder method to check uniqueness. This method uses 'AND' relation between parameters in the query, which causes the problem.

Is it possible to use this validation constraint for my problem somehow, or I have to validate it another way?

Solution 1

What about :

/**
 * @ORM\Table(name="mytable")
 * @ORM\Entity
 * @DoctrineAssert\UniqueEntity(fields = "fieldA")
 * @DoctrineAssert\UniqueEntity(fields = "fieldB")
 */
class myClass

?

Solution 2

It should be:

/**
 * @ORM\Table(name="mytable")
 * @ORM\Entity
 * @DoctrineAssert\UniqueEntity(fields = "fieldA")
 * @DoctrineAssert\UniqueEntity(fields = "fieldB")
 */
class myClass

By doing

 * @DoctrineAssert\UniqueEntity(fields = {"fieldA", "fieldB"})

it will check if there aren't any rows with both fields the same.

So suppose you already have a record in the database with values:

fieldA = 'value_a', fieldB = 'value_b'

Now when you try to submit another one with values (fieldA = 'value_a', fieldB = 'value_c') from a form, Symfony2 generates a query to check uniqueness:

SELECT ... FROM ... WHERE fieldA = ? AND fieldB = ? ('value_a', 'value_c')

and this will pass, cause it does not match a row with

fieldA = 'value_a', fieldB = 'value_b'

only when yo submit another one with values (fieldA = 'value_a', fieldB = 'value_b') from a form, the validation won't pass.

This is the way it should work and how it is explained in the documentation: http://symfony.com/doc/current/reference/constraints/UniqueEntity.html#fields

Comments

[csabavegso]

Thank you, it does the job! The only problem, is that each assert takes another query, but I think I can live with it.

[webda2l]

Otherwise, you have always the solution to write you own Validator, with inspiration of the UniqueEntity Validator

[BaDr Amer]

but be aware that This constraint doesn't provide any protection against race conditions. They may occur when another entity is persisted by an external process after this validation has passed and before this entity is actually persisted in the database. as mentioned in the link you provide.