Best way to delete child record in Yii

Yii 1.1 General Discussion
1

Hi,

What is the best way to delete a record and all of its related child elements in Yii.

OR

SQL or some other technology what makes life easier.

James.

2

Use as storage engine InnoDb.

This allows you to use foreign key. In phpmyadmin they can be managed with the link "show relations".

Now you have just to set foreign key on delete cascade, and magically the delete of the parent element will delete all childs.

3

Ok great, and does this work with children of children?

4

ON DELETE CASCADE works for all child records and their child records as long as you set cascade option in every corresponding table

5

Thanks.

6

Just be carefull with setting the CASCADE, verify that you didn’t set CASCADE on something that should not be deleted :)

7

Hi James,

This will fire the before/afterDelete() methods in the child records too so that you can do pre/post actions.

Parent Model:




    private $idCache; 


    public function beforeDelete()

    {

        $this->idCache = $this->id;


        return parent::beforeDelete();

    }


    public function afterDelete()

    {

        $criteria = new CDbCriteria(array(

                'condition' => 'parent_id=:parentId',

                'params' => array(

                    ':parentId' => $this->idCache),

            ));


        $children = Child::model()->findAll($criteria);


        foreach ($children as $child)

        {

            $child->delete();

        }


        parent::afterDelete();

    }

Matt

8

Another good idea.

9

Just don’t forget to wrap the whole deletion process into a transaction to ensure that your database remains in a consistent state when something goes wrong in php.

10

What do you mean transaction? How is this achieved?

Which one do you thinks best, afterDelete or delete cascade?

11

A database transaction is represented by an instance of CDbTransaction in Yii.

[/size][/color][/size][/color]

I personally prefer foreign key constraints in the database when possible. Database engines were designed to maintain data integrity, PHP wasn’t :)

12

Are foreign key constraints a part of cascade delete?

I assume foreign key constraints are what tells the database about the relation between two tables etc?

13

@zaccaria how do you cascade backwards? i.e. delete a child and its parent (and then all the other children of the parent) get deleted?

often development you need a “child” record deleted and you don’t care about what data you lose. you just need it deleted immediately. does SQL have a feature for that? how would you implement this? Maybe with a little Yii component that generates you a little page to make these deletions. Basically you enter a row id, and it will find the parent and delete it, which will delete the row you wanted deleted (and its siblings, and so on).

14

– if you ask for best way ,

the best way is to give relation to database tables and use ON DELETE CASCADE for table relation.

15

– if you ask for best way ,

the best way is to give relation to database tables and use ON DELETE CASCADE for table relation.