Doctrine 1
  1. Doctrine 1
  2. DC-560

Object1->Unlink(Relation, Id2) doesn't work, if primary key order in table of m2m reference isnt 1:Object 2: Id to unlink, when relation set to EQUAL= TRUE

    Details

    • Type: Bug Bug
    • Status: Reopened
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 1.2.0
    • Fix Version/s: None
    • Component/s: Relations
    • Labels:
      None
    • Environment:
      Windows XP, Apache 2.2, PHP 5, Doctrine 1.2 on Symfony 1.4

      Description

      Object1->Unlink(Relation, Id2) doesn't work, if primary key order in table of m2m reference isnt 1:Object 2: Id to unlink, when relation set to EQUAL= TRUE

      User:
        actAs: 
          Timestampable: ~
        columns:    
          name:         { type: string(255), notnull: false }
          last_name:    { type: string(255), notnull: false }
          picture:      { type: string(255), notnull: false }
          birthday:     { type: timestamp, notnull: false }
          gender:       { type: string(255) }
          rating:       { type: integer, default:0} 
          num_comments: { type: integer, default:0} 
          num_groups:   { type: integer, default:0} 
        inheritance:
          extends: Member
          type: column_aggregation
          keyField: type
          keyValue: 1                    
        relations:
          Friends:    { onDelete: CASCADE, class: User, foreignAlias: Friends, refClass: FriendReference, local: user1, foreign: user2, equal: true }
          Request:    { onDelete: CASCADE, class: User, foreignAlias: Pending, refClass: PendingReference, local: user1, foreign: user2 }
          Blocker:    { onDelete: CASCADE, class: User, foreignAlias: Blocked, refClass: BlockedReference, local: user1, foreign: user2 }
      
      
       
      			if(isset($refToRemove))
      			{
      				switch ($refToRemove)
      				{
      					case 'FriendReference': $references = $currentUser->Friends; $relation = 'Friends';	break;
      					case 'PendingReference': $references = $currentUser->Pending; $relation = 'Pending'; break;
      					case 'BlockerReference': $references = $currentUser->Blocker; $relation = 'Blocker'; break;
      					case 'RequestReference': $references = $currentUser->Request; $relation = 'Request'; break;
      				}
      				 
      				foreach($references as $reference)
      				{
      
      					if ($reference->id == $user2->id)
                              $currentUser->unlink($relation, array($user2->id), $now = true);
      				}
                      
      			}
      

      when you count relations from one side and the other it show that when set to EQUAL = TRUE relations are reciprocal, but unlink still minds primary keys order in order to function

        Activity

        Hide
        Jonathan H. Wage added a comment -

        I am unable to reproduce the issue. When I test everything works as expected. We really need some more information and a test case in order to be sure of any issue.

        Show
        Jonathan H. Wage added a comment - I am unable to reproduce the issue. When I test everything works as expected. We really need some more information and a test case in order to be sure of any issue.
        Hide
        Ian Ricketson added a comment - - edited

        I was able to reproduce this issue in Doctrine 1.2. The issue is a two part scenerio, which makes it kind of hard to debug. But here is how to reproduce it.

        1.) Lets assume you have a Product model with ID 14.
        2.) Lets assume you have an "equal" M:M table that links to itself, (product_product_group). "product_id_1" and "product_id_2" are a compound primary key which link back to the Product model.
        3.) Lets assume you that Product 14 is currently linked to products with IDs 2, 5, and 13.
        4.) Assuming $object is an instance of Product id 14, call $object->unlink(array(2));

        1. SQL QUERY LOG for Step 4.)
          482 Query START TRANSACTION
          482 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2'))
          482 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5'
          482 Query UPDATE product_product_group SET product_id_1 = '13' WHERE product_id_1 = '14' AND product_id_2 = '13'
          482 Query DELETE FROM product_product_group WHERE product_id_2 = '2' AND product_id_1 = '14'
          482 Query COMMIT

        The result is as follows:
        a.) Doctrine properly unlinks ID 2
        b.) Doctrine updates the M:M table and sets the remaining two IDs (5 and 13) and links them to themselves (it shouldn't run these updates). Now, all we have in our database are two links (5-5 and 13-13).

        5.) Now, Product ID 14 has no links anymore in the M:M table. So we call $object->link(array(2, 5, 13));

        1. SQL QUERY LOG for Step 5.)
          512 Query START TRANSACTION
          512 Query SELECT ... FROM to_product_id t WHERE (t.id IN ('13', '2', '5'))
          512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('2', '14')
          512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('5', '14')
          512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('13', '14')
          512 Query COMMIT

        This works as expected.

        6.) Now, in our database, Product ID 14 properly links to Ids 2, 5, and 13. BUT IDs 5 and 13 also have a link to themselves as a byproduct of the UPDATE queries called in step 4.).
        7.) Now, we call $object->unlink(array(2)) again.

        1. SQL QUERY LOG for Step 7.)
          432 Query START TRANSACTION
          432 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2'))
          432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '2' AND product_id_2 = '5'
          432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5'
          432 Query ROLLBACK

        The following error occurs:

        1. Mar 30 20:40:41 symfony [err] exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '5-5' for key 1' in /path/to/symfony/1.4/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php:1082

        So basically, it repeated the same queries from step 4.), however this time around, when attempting to set ID 5 to itself, it fails because there is already an entry this pair in the database. Since we have a compound primary key, this non-unique combination is not allowed. I don't know why its trying to run these UPDATE queries, but its definitely something to do with thre relationship being "equal".

        Show
        Ian Ricketson added a comment - - edited I was able to reproduce this issue in Doctrine 1.2. The issue is a two part scenerio, which makes it kind of hard to debug. But here is how to reproduce it. 1.) Lets assume you have a Product model with ID 14. 2.) Lets assume you have an "equal" M:M table that links to itself, (product_product_group). "product_id_1" and "product_id_2" are a compound primary key which link back to the Product model. 3.) Lets assume you that Product 14 is currently linked to products with IDs 2, 5, and 13. 4.) Assuming $object is an instance of Product id 14, call $object->unlink(array(2)); SQL QUERY LOG for Step 4.) 482 Query START TRANSACTION 482 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2')) 482 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5' 482 Query UPDATE product_product_group SET product_id_1 = '13' WHERE product_id_1 = '14' AND product_id_2 = '13' 482 Query DELETE FROM product_product_group WHERE product_id_2 = '2' AND product_id_1 = '14' 482 Query COMMIT The result is as follows: a.) Doctrine properly unlinks ID 2 b.) Doctrine updates the M:M table and sets the remaining two IDs (5 and 13) and links them to themselves (it shouldn't run these updates). Now, all we have in our database are two links (5-5 and 13-13). 5.) Now, Product ID 14 has no links anymore in the M:M table. So we call $object->link(array(2, 5, 13)); SQL QUERY LOG for Step 5.) 512 Query START TRANSACTION 512 Query SELECT ... FROM to_product_id t WHERE (t.id IN ('13', '2', '5')) 512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('2', '14') 512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('5', '14') 512 Query INSERT INTO to_product_id_to_product_id_group (product_id_2, product_id_1) VALUES ('13', '14') 512 Query COMMIT This works as expected. 6.) Now, in our database, Product ID 14 properly links to Ids 2, 5, and 13. BUT IDs 5 and 13 also have a link to themselves as a byproduct of the UPDATE queries called in step 4.). 7.) Now, we call $object->unlink(array(2)) again. SQL QUERY LOG for Step 7.) 432 Query START TRANSACTION 432 Query DELETE FROM product_product_group WHERE (product_id_1 = '14' AND product_id_2 IN ('2')) 432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '2' AND product_id_2 = '5' 432 Query UPDATE product_product_group SET product_id_1 = '5' WHERE product_id_1 = '14' AND product_id_2 = '5' 432 Query ROLLBACK The following error occurs: Mar 30 20:40:41 symfony [err] exception 'Doctrine_Connection_Mysql_Exception' with message 'SQLSTATE [23000] : Integrity constraint violation: 1062 Duplicate entry '5-5' for key 1' in /path/to/symfony/1.4/lib/plugins/sfDoctrinePlugin/lib/vendor/doctrine/Doctrine/Connection.php:1082 So basically, it repeated the same queries from step 4.), however this time around, when attempting to set ID 5 to itself, it fails because there is already an entry this pair in the database. Since we have a compound primary key, this non-unique combination is not allowed. I don't know why its trying to run these UPDATE queries, but its definitely something to do with thre relationship being "equal".
        Hide
        Jonathan H. Wage added a comment -

        It is hard to decipher the problem. It is best and most clear to provide a test case.

        Show
        Jonathan H. Wage added a comment - It is hard to decipher the problem. It is best and most clear to provide a test case.
        Hide
        Ian Ricketson added a comment -

        I know this is old, and is likely to never be resolved, but the problem does still exist.

        Basically, "unlinking" an equal relation only unlinks one side of the relationship:

        DELETE FROM user_user_group WHERE (user_id_1 = '20' AND user_id_2 IN ('9'))

        However, if the relationship was created from the other side (i.e. user_id_1 = 9 and user_id_2 = 20), then it doesn't properly get rid of the relationship. Again, this only applies when a relationship is marked as "equal".

        Here is another post about the same issue, with their work-around:
        http://stackoverflow.com/questions/1385281/how-do-i-remove-a-self-referencing-nn-relation-in-doctrine

        Show
        Ian Ricketson added a comment - I know this is old, and is likely to never be resolved, but the problem does still exist. Basically, "unlinking" an equal relation only unlinks one side of the relationship: DELETE FROM user_user_group WHERE (user_id_1 = '20' AND user_id_2 IN ('9')) However, if the relationship was created from the other side (i.e. user_id_1 = 9 and user_id_2 = 20), then it doesn't properly get rid of the relationship. Again, this only applies when a relationship is marked as "equal". Here is another post about the same issue, with their work-around: http://stackoverflow.com/questions/1385281/how-do-i-remove-a-self-referencing-nn-relation-in-doctrine

          People

          • Assignee:
            Jonathan H. Wage
            Reporter:
            Mariano Ramon
          • Votes:
            1 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated: