Doctrine 1
  1. Doctrine 1
  2. DC-187

The Unique does not work correctly when using "SoftDelete"

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.4, 1.1.5
    • Fix Version/s: 1.2.0-BETA3
    • Component/s: Behaviors
    • Labels:
      None
    • Environment:
      WindowsXP, WAMP 2.0

      Description

      Scenario:

      class User extends Doctrine_Record
      {
      public function setTableDefinition()

      { $this->hasColumn('username', 'string', 255); $this->hasColumn('email', 'string', 255); $this->unique('username', 'email'); }

      public function setUp()

      { $this->actAs('SoftDelete'); }

      }

      Test:

      //create User
      $user = new User();
      $user->username = 'Fabian';
      $user->email = 'fabian@test.com';
      $user->save();

      ...
      ...
      // delete user
      $user = Doctrine_Query::create()
      ->select('*')
      ->from('User')
      ->where("id=1")
      ->fetchOne();
      $user->delete(); //this mark as deleted the user (delete_at field)

      ....
      ....

      //create User with same data
      $user = new User();
      $user->username = 'Fabian';
      $user->email = 'fabian@test.com';
      $user->save();

      // This throw error by the "unique"

        Activity

        Hide
        Fabian Brussa added a comment - - edited

        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.

        Show
        Fabian Brussa added a comment - - edited 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.
        Hide
        Jonathan H. Wage added a comment -

        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.

        Show
        Jonathan H. Wage added a comment - 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.
        Hide
        Jonathan H. Wage added a comment -

        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.

        Show
        Jonathan H. Wage added a comment - 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.
        Hide
        Fabian Brussa added a comment -

        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.

        Show
        Fabian Brussa added a comment - 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.
        Hide
        Jonathan H. Wage added a comment -

        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.

        Show
        Jonathan H. Wage added a comment - 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.

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Fabian Brussa
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: