SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)

    ActiveRecord Interface for delete

    What would be the best way to define that related models should be cascaded or skipped when a parent is removed?

    • User
    • Blog Entry
      • BlogComment
    • BlogComment


    A BlogComment would not be deleted when a User is deleted. However, it would be removed when a BlogEntry is removed.

    So what would be the best way to define this inside the model classes?

    PHP Code:
    class BlogComment extends ActiveRecord {

          public static 
    $cascadeDelete = array(
                 
    'User'=>array(false,false// skip, don't cascade when parent is User
                
    ,'BlogEntry'=>array(true,true// delete when parent is removed and cascade
          
    )


    That is what I'm currently thinking but I'm looking for possible alternatives.

    The other thing I would like to implement is a way to define at run time the cascade.

    PHP Code:
    $user = new User(12);
    $user->delete(array('BlogComment'=>array('User'=>array('false','false')))); 
    That seems like a decent approach. In that a BlogComment with a direct parent of User would not be cascaded or deleted.

    Based on the parent any related models can be excluded from being deleted or cascaded. However, I'm not sure on the best interface which to make this possible from the developers perspective.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    For me, I'd much prefer to see some function parameters with default values rather than a specifically structured array, it just seems clearer to me from an API POV.

    Although, on second thoughts, it wouldn't be very extendible, if for example you added an additional requirement like UserPics. So would therefore violate the open/closed principle.

    So, thinking this through a little, should the user object be tasked with removing related items?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    The other way I was thinking.
    PHP Code:
    class BlogComment {

        public static 
    $cascadeOnDelete = array(
            
    'User'=>false
        
    )


        public static 
    $deleteOnDelete = array(
            
    'User'=>false
        
    )


    DeleteonDelete: key represents direct parent. So when a User is deleted the users blog comments will not be deleted.

    cascadeOnDelete: key represents direct parent. So when a User is deleted the delete will not cascade to children of blog comments if any exists.

    However, the BlogComments associated with a BlogEntry would be deleted and cascaded by default because nothing exists pertaining those requirements.

    Quote Originally Posted by SilverBulletUK
    For me, I'd much prefer to see some function parameters with default values rather than a specifically structured array
    Not sure what you mean considering everything is dynamic. There are no specific requirements. The requirements are dynamic based on the given active record instance that is being acted upon.

    The node within the above hierarchy is only unique by looking to the parent and its child.

    For example, a User and BlogEntry both have BlogComments. So the only way to control whether a BlogComment is removed is by comparing the parent. Otherwise all BlogComments for Users and BlogEntries would be removed.

    By providing a way to essentially say:

    if($parentNode == 'User' && $node == 'BlogComment') return; // skip this node

    However, when the parent is BlogEntry the method would not return and would remove all the BlogEntries associated blog comments. I am looking for a way to control this through a simple API.

  4. #4
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    The actual API to delete a Record and its associations is nothing new.

    PHP Code:
    $user = new User(12); // gets user with primary key of 12
    $user->delete();  // deletes user 
    The SQL and binding data that would be outputted at the current moment:

    HTML Code:
    DELETE FROM `blog_comments` WHERE `blog_entry_id` IN (SELECT DISTINCT t0.`id` FROM `blog_entries` AS t0 INNER JOIN `users` AS t1 ON t0.`user_id` = t1.`id` WHERE (t1.`id` = ?))
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `blog_entries` WHERE (`user_id` = ?)
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `blog_comments` WHERE (`user_id` = ?)
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `users` WHERE (`id` = ?)
    
    Array
    (
        [0] => 12
    )
    Now what I would like is way to pass options to determine whether or not to include associations and/or cascade them. So that for instance if the node is equal to BlogComment and the parent is User skip and/or don't cascade.

    HTML Code:
    DELETE FROM `blog_comments` WHERE `blog_entry_id` IN (SELECT DISTINCT t0.`id` FROM `blog_entries` AS t0 INNER JOIN `users` AS t1 ON t0.`user_id` = t1.`id` WHERE (t1.`id` = ?))
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `blog_entries` WHERE (`user_id` = ?)
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `users` WHERE (`id` = ?)
    
    Array
    (
        [0] => 12
    )
    Notice how the users associated blog comments are being removed from the delete now. That is what I would like to be able to control through the delete method along with whether to cascade.

    A example of the purpose of the cascade would be delete the users blog_entries but keep the blog_entries comments.

    HTML Code:
    DELETE FROM `blog_entries` WHERE (`user_id` = ?)
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `blog_comments` WHERE (`user_id` = ?)
    
    Array
    (
        [0] => 12
    )
    
    
    DELETE FROM `users` WHERE (`id` = ?)
    
    Array
    (
        [0] => 12
    )
    Notice how this query would be removed because the blog_entries would not cascade when the parent is user perhaps.

    HTML Code:
    DELETE FROM `blog_comments` WHERE `blog_entry_id` IN (SELECT DISTINCT t0.`id` FROM `blog_entries` AS t0 INNER JOIN `users` AS t1 ON t0.`user_id` = t1.`id` WHERE (t1.`id` = ?))
    
    Array
    (
        [0] => 12
    )

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Maybe I should explain how some of this actually works.


    The class responsible for cascading the relationship tree is called ActiveRecordCascade. ActiveRecordCascade may take one parameter upon being constructed that is an instance of the IActiveRecordCascadeAction interface. ActiveRecordDelete (show below) implements IActiveRecordCascadeAction. The interface method of iActiveRecordCascadeAction is called on every node within the relationship hierarchy. If the doSomething() method of the IActiveRecordCascadeAction return false then the node becomes a leaf element and its children are omitted. Therefore, by comparing the parent to the current node inside the doSomething method it is possible to remove a entire branch. It is also possible to skip a node by just returning true and not running the methods to build the SQL in the ActiveRecordDelete class. What I am looking for is a simple API to control this from outside these classes.

    PHP Code:
    class ActiveRecordCascade {

        protected 
    $action;

        public function 
    __construct(IActiveRecordCascadeAction $action) {
        
            
    $this->action $action;
        
        }
        
        public function 
    cascade(ActiveRecordCascadeNode $node,$nodes null) {
            
            if(
    $this->action->doSomething($node,$nodes)===true) {
        
                
    $this->_resolveHasOne($node,$nodes);
                
    $this->_resolveHasMany($node,$nodes);
                
    $this->_resolveBelongsToAndHasMany($node,$nodes);
            
            }
        
        }

        protected function 
    _resolveHasOne(ActiveRecordCascadeNode $node,$nodes null) {

            if(
    $node->getConfig()->hasOne()===true) {
        
                foreach(
    $node->getConfig()->getHasOne() as $model) {
                
                    
    $class Inflector::classify($model);
                    
    $relatedConfig ActiveRecordModelConfig::getModelConfig($class);    
                    
    $relatedNode = new ActiveRecordCascadeNode($relatedConfig);
                    
    $this->_collectRecord($node,$relatedNode,$model);
                    
    $relatedNodes is_null($nodes)?array():$nodes;
                    
    array_unshift($relatedNodes,$node);
                    
    $this->cascade($relatedNode,$relatedNodes);
            
                }
        
            }

        }

        protected function 
    _resolveHasMany(ActiveRecordCascadeNode $node,$nodes null) {

            if(
    $node->getConfig()->hasMany()===true) {
        
                foreach(
    $node->getConfig()->getHasMany() as $model) {
                    
                    
    $class Inflector::classify($model);
                    
    $relatedConfig ActiveRecordModelConfig::getModelConfig($class);    
                    
    $relatedNode = new ActiveRecordCascadeNode($relatedConfig);
                    
    $this->_collectRecords($node,$relatedNode,$model);
                    
    $relatedNodes is_null($nodes)?array():$nodes;
                    
    array_unshift($relatedNodes,$node);
                    
    $this->cascade($relatedNode,$relatedNodes);
            
                }
        
            }

        }
        
        protected function 
    _resolveBelongsToAndHasMany(ActiveRecordCascadeNode $node,$nodes null) {
        
            if(
    $node->getConfig()->hasBelongsToAndHasMany()===true) {
            
                foreach(
    $node->getConfig()->getBelongsToAndHasMany() as $index=>$reference) {
                
                    
    $class Inflector::classify($reference[1]);
                    
    $relatedConfig ActiveRecordModelConfig::getModelConfig($class);    
                    
    $relatedNode = new ActiveRecordCascadeNode($relatedConfig);
                    
    $this->_collectRecords($node,$relatedNode,$reference[1]);
                    
    $relatedNodes is_null($nodes)?array():$nodes;
                    
    array_unshift($relatedNodes,$node);
                    
    $this->cascade($relatedNode,$relatedNodes);
                
                }
            
            }
        
        }
        
        protected function 
    _collectRecords(ActiveRecordCascadeNode $node,ActiveRecordCascadeNode $relatedNode,$property) {
            
            if(
    $node->hasRecords()===true) {
                foreach(
    $node->getRecords() as $record) {
                
                    if(
    $record->hasProperty($property)===true) {
                    
                        foreach(
    $record->getProperty($property) as $relatedRecord) {
                            
    $relatedNode->addRecord($relatedRecord);
                        }
                    
                    }
                
                }
            }
        
        }
        
        protected function 
    _collectRecord(ActiveRecordCascadeNode $node,ActiveRecordCascadeNode $relatedNode,$property) {
            
            if(
    $node->hasRecords()===true) {
            
                foreach(
    $node->getRecords() as $record) {
                
                    if(
    $record->hasProperty($property)===true) {
                    
                        
    $relatedNode->addRecord($record->getProperty($property));
                        
                    }
                }
            }
        
        }

    }
    ?> 
    PHP Code:
    <?php
    interface IActiveRecordCascadeAction {

        public function 
    doSomething(ActiveRecordCascadeNode $node,$nodes=null); // bool

    }
    ?>
    PHP Code:
    class ActiveRecordDelete implements IActiveRecordCascadeAction {

        protected 
    $queries;
        
        public function 
    __construct() {
            
            
    $this->queries = array();
            
        }
        
        public function 
    getQueries() {
        
            return 
    $this->queries;
            
        }
        
        public function 
    query(PDO $db) {
        
            
    $total count($this->queries);        
            if(
    $total==0) return;
            
            for(
    $i=($total-1);$i>=0;$i--) {
            
                foreach(
    $this->queries[$i] as $query) {
                
                    try {
                    
                        echo 
    '<p>',$query->getSql(),'</p>';
                        echo 
    '<pre>',print_r($query->getData()),'</pre>';
                        
    //$query->query($db);
                    
                    
    } catch(Exception $e) {
                    
                        return 
    false;
                    
                    }
                    
                }
            
            }
            
            return 
    true;        
        
        }

        public function 
    doSomething(
            
    ActiveRecordCascadeNode $node
            
    ,$nodes=null
        
    ) {
        
            
    $nodes is_null($nodes)?array():$nodes;
            
            
    $countNodes count($nodes); 
            
    $query = new ActiveRecordQuery();
            
            if(
    $countNodes>1) {    

                
    $field $node->getConfig()->getRelatedField($nodes[0]->getConfig());    
                
                if(empty(
    $field)) {
                    throw new 
    Exception('Unable to resolve relationship between '.$node->getConfig()->getClassName().' and '.$nodes[0]->getConfig()->getClassName().' inside '.__CLASS__.' class method '.__METHOD__ .' line '.__LINE__.'.');
                    return 
    false;
                }
                
                
    $subquery $this->_makeSubquery($nodes,$query);
                
    $sql 'DELETE FROM `'.$node->getConfig()->getTable().'` WHERE `'.$field.'` IN  ('.$subquery.')';
            
            } else if(
    $countNodes==1) {
            
                
    $field $node->getConfig()->getRelatedField($nodes[0]->getConfig());
            
                if(empty(
    $field)) {
                    throw new 
    Exception('Unable to resolve relationship between '.$node->getConfig()->getClassName().' and '.$nodes[0]->getConfig()->getClassName().' inside '.__CLASS__.' class method '.__METHOD__.' line '.__LINE__.'.');
                    return 
    false;
                }
            
                
    $sql 'DELETE FROM `'.$node->getConfig()->getTable().'` '.$this->_makeWhereClause(array($node,$nodes[0]),$query,false,$field);
            
            } else {
            
                
    $sql 'DELETE FROM `'.$node->getConfig()->getTable().'` '.$this->_makeWhereClause(array($node),$query,false);
            
            }
            
            
    $query->setSql($sql);
            
            if(
    array_key_exists($countNodes,$this->queries)) {
                
    $this->queries[$countNodes][] = $query
            } else {
                
    $this->queries[$countNodes] = array($query); 
            }
            
            return 
    true;
            
        
        }
        
        protected function 
    _makeSubQuery($nodes,ActiveRecordQuery $query) {
        
            
    $str '';    
            
    $p=null;
        
            foreach(
    $nodes as $key=>$c) {
                
                if(!
    is_null($p)) {
                
                    
    $str.= ' INNER JOIN `'.$c->getConfig()->getTable().'` AS t'.$key.' ON t'.($key-1).'.`'.$p->getConfig()->getRelatedField($c->getConfig()).'` = t'.$key.'.`'.$c->getConfig()->getRelatedField($p->getConfig()).'`';
                    
    $p $c;
                
                } else {
                
                    
    $str.= 'SELECT DISTINCT t'.$key.'.`'.$c->getConfig()->getPrimaryKey().'` FROM `'.$c->getConfig()->getTable().'` AS t'.$key;
                    
    $p $c;
                
                }
                
            }    
        
            return 
    $str.$this->_makeWhereClause($nodes,$query);
        
        
        }
        
        protected function 
    _makeWhereClause($nodes,ActiveRecordQuery $query,$subquery=true,$field=null) {
        
            
    $countNodes = (count($nodes)-1);
            
    $filters = array();
        
            for(
    $i=$countNodes;$i>=0;$i--) {
            
                if(
    $nodes[$i]->hasRecords()===true) {
                    
                    
    $placeholders = array();
                    
    $primaryKey $nodes[$i]->getConfig()->getPrimaryKey();
                    foreach(
    $nodes[$i]->getRecords() as $record) {
                        
    $query->addData($record->getProperty($primaryKey));
                        
    $placeholders[] = '?';
                    }
                    
                    if(!empty(
    $placeholders)) {
                    
                        
    $field = !is_null($field) && $i==$countNodes?$field:$primaryKey;
                    
                        if(
    $subquery===true) {
                            
                            if(
    count($placeholders)==1) {
                                
    $filters[] = '(t'.$i.'.`'.$field.'` = ?)';
                            } else {
                                
    $filters[] = '(t'.$i.'.`'.$field.'` IN ('.implode(',',$placeholders).'))';
                            }
                            
                        } else {
                            
                            if(
    count($placeholders)==1) {
                                
    $filters[] = '(`'.$field.'` = ?)';
                            } else {
                                
    $filters[] = '(`'.$field.'` IN ('.implode(',',$placeholders).'))';
                            }
                            
                        }
                    }
                    
                }
            
            }
            
            return empty(
    $filters)?'':' WHERE '.implode(' AND ',$filters);
        
        }

    }
    ?> 
    So now the actual code that would go inside the ActiveRecord delete method would be the following:

    PHP Code:
        public function delete() {
            
            
    $config ActiveRecordModelConfig::getModelConfig(get_class($this));
            
    $node = new ActiveRecordCascadeNode($config);
            
    $node->addRecord($this);
            
            try {
            
                
    $delete = new ActiveRecordDelete();
                
    $cascade = new ActiveRecordCascade($delete);
                
    $cascade->cascade($node);
            
            } catch(
    Exception $e) {
                
                throw new 
    Exception('Error initializing delete. Exception caught and rethrown from line '.__LINE__.' in class '.__CLASS__.' inside method '.__METHOD__.': '.$e->getMessage());
                return 
    false;
            
            }
            
            try {
            
                if(
    $delete->query(self::getConnection())===true) {
                
                    
    $unset = new ActiveRecordDeactivate();
                    
    $cascade = new ActiveRecordCascade($unset);
                    
    $cascade->cascade($node);
                    return 
    true;
                    
                } else {
                
                    return 
    false;
                
                }
            
            } catch(
    Exception $e) {
            
                throw new 
    Exception('Error executing delete queries. Exception caught and rethrown from line '.__LINE__.' in class '.__CLASS__.' inside method '.__METHOD__.': '.$e->getMessage());
                return 
    false;
            
            }
        
        } 
    Currently there isn't a way to control what will get deleted within the tree hierarchy. Every dependency is removed and cascaded. What I would like is a way to control that based on a parent to child relationship from the delete() method of the ActiveRecord instance so it may be decided at run time. I would also like to be able to contorl it from the model files themselves where the runtime options would override those in the model files.

  6. #6
    SitePoint Addict Mastodont's Avatar
    Join Date
    Mar 2007
    Location
    Czech Republic
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oddz: I must admit that I completely do not understand why do you try to solve this on application level. Almost each database engine has something called "referential integrity" and ON DELETE CASCADE or similar command ...

  7. #7
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Some such as MYISAM engine do not though.

  8. #8
    PHP/Rails Developer Czaries's Avatar
    Join Date
    May 2004
    Location
    Central USA
    Posts
    806
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    ... MYISAM ...
    *shudder*

  9. #9
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    Another good reason is to be able to remove something through something else. So that one could retrieve a user and remove only certain items that relate. The simplest form of this could be removing all children.

    $user = new User(4);
    $user->removeChildren();

    I hate the method name removeChildren, but the implementation of this would be as simple as returning as not adding the query for the item at depth 0.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •