The Unique does not work correctly when using "SoftDelete"

I think you should add deleted_at to the array of fields to be unique by.

Jonathan, but deleted_at is null, therefore can not be part of the array of fields .

I tried adding it does not work, take all the data as different

I see. If that is the case, I don't see of any way to resolve this. Do you?

mmm, if it is possible to know whether the class acts as SoftDelete at the time of validating the unique, could skip if is marked as deleted (deleted_at not null)

It is not Doctrine, it is the database that throws the error due to the unique index.

Jonathan, in the database is not created the unique index, only in definition of the class users with ($this->unique('username', 'email'); )

I'm seeing if is possible to consider the "deleted_at" in then Validate Method in Doctrine_Validator_Unique

Adding these lines in the method "validate", class "Doctrine_Validator_Unique", just before a

$stmt  = $table->getConnection()->getDbh()->prepare($sql);

works fine
.

 
$arr = $table->getTemplates();
if ( array_key_exists("Doctrine_Template_SoftDelete",$arr))
{
	$fieldNameDeleteAt = $arr["Doctrine_Template_SoftDelete"]->getOption("name"); 
	$sql .= " AND $fieldNameDeleteAt IS NULL";
}

Be okay to do this here?

Prerequisite: not defined unique index in the database.

I am not sure, because the unique index is always created in the database. Can you even disable it? So adding this code is useless since the index is created by default and will throw the exception.

This code:

$this->unique('username', 'email');

Does two things, it adds a unique validator for the columns, but it also adds a unique index in the mapping information which gets created in the database if it supports unique indexes.

Hi Jonathan,

Thanks for your swift response!

I understand that the indexes are created automatically in the database and that is what I think should (or could) work differently.

When creating an index for multiple fields in combination with the softdelete I would like to propose to not create the indexes in the database and depend on the doctrine Unique class to handle this. In this case the doctrine Unique class should take into account that softdelete is activated and thus only query the not deleted records to check if they are unique.

I hope you see this as a solution for a common problem and are willing to take it into consideration.

I would rather not implement specific hacks for individual behaviors. Instead I think we need more generic solutions.

To get around this you can do the following now:

        $this->unique(
            array('username', 'email'),
            array('where' => "deleted_at IS NULL"),
            false
        );

The 2nd argument is an array of options for the unique validator. It already accepted a "where" key so you can add deleted_at IS NULL to the where condition. The 3rd argument "false" is so that it does not create the unique index in the database.