Rectangle 27 0

php On delete cascade with doctrine2?


# doctrine orm:schema-tool:create --dump-sql
<?php
namespace Entities;

use Doctrine\Common\Collections\ArrayCollection;

/**
 * @Entity
 * @Table(name="contacts")
 */
class Contact 
{

    /**
     *  @Id
     *  @Column(type="integer", name="contact_id") 
     *  @GeneratedValue
     */
    protected $id;  

    /** 
     * @Column(type="string", length="75", unique="true") 
     */ 
    protected $name; 

    /** 
     * @OneToMany(targetEntity="Phonenumber", mappedBy="contact")
     */ 
    protected $phonenumbers; 

    public function __construct($name=null)
    {
        $this->phonenumbers = new ArrayCollection();

        if (!is_null($name)) {

            $this->name = $name;
        }
    }

    public function getId()
    {
        return $this->id;
    }

    public function setName($name)
    {
        $this->name = $name;
    }

    public function addPhonenumber(Phonenumber $p)
    {
        if (!$this->phonenumbers->contains($p)) {

            $this->phonenumbers[] = $p;
            $p->setContact($this);
        }
    }

    public function removePhonenumber(Phonenumber $p)
    {
        $this->phonenumbers->remove($p);
    }
}

<?php
namespace Entities;

/**
 * @Entity
 * @Table(name="phonenumbers")
 */
class Phonenumber 
{

    /**
    * @Id
    * @Column(type="integer", name="phone_id") 
    * @GeneratedValue
    */
    protected $id; 

    /**
     * @Column(type="string", length="10", unique="true") 
     */  
    protected $number;

    /** 
     * @ManyToOne(targetEntity="Contact", inversedBy="phonenumbers")
     * @JoinColumn(name="contact_id", referencedColumnName="contact_id", onDelete="CASCADE")
     */ 
    protected $contact; 

    public function __construct($number=null)
    {
        if (!is_null($number)) {

            $this->number = $number;
        }
    }

    public function setPhonenumber($number)
    {
        $this->number = $number;
    }

    public function setContact(Contact $c)
    {
        $this->contact = $c;
    }
} 
?>

<?php

$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);

$contact = new Contact("John Doe"); 

$phone1 = new Phonenumber("8173333333");
$phone2 = new Phonenumber("8174444444");
$em->persist($phone1);
$em->persist($phone2);
$contact->addPhonenumber($phone1); 
$contact->addPhonenumber($phone2); 

$em->persist($contact);
try {

    $em->flush();
} catch(Exception $e) {

    $m = $e->getMessage();
    echo $m . "<br />\n";
}
CREATE TABLE contacts
 (contact_id BIGINT AUTO_INCREMENT NOT NULL,
 name VARCHAR(75) NOT NULL,
 PRIMARY KEY(contact_id)) ENGINE = InnoDB;

CREATE TABLE phone_numbers
 (phone_id BIGINT AUTO_INCREMENT NOT NULL,
  phone_number CHAR(10) NOT NULL,
 contact_id BIGINT NOT NULL,
 PRIMARY KEY(phone_id),
 UNIQUE(phone_number)) ENGINE = InnoDB;

ALTER TABLE phone_numbers ADD FOREIGN KEY (contact_id) REFERENCES \
contacts(contact_id) ) ON DELETE CASCADE;
DELETE TABLE contacts as c WHERE c.id=1; /* delete cascades to phone_numbers */
INSERT INTO table contacts(name) VALUES('Robert Smith');
INSERT INTO table phone_numbers(phone_number, contact_id) VALUES('8963333333', 1);
INSERT INTO table phone_numbers(phone_number, contact_id) VALUES('8964444444', 1);

By adding "ON DELETE CASCADE" to the foreign key constraint, phone_numbers will automatically be deleted when their associated contact is deleted.

Here is simple example. A contact has one to many associated phone numbers. When a contact is deleted, I want all its associated phone numbers to also be deleted, so I use ON DELETE CASCADE. The one-to-many/many-to-one relationship is implemented with by the foreign key in the phone_numbers.

Now when a row in the contacts table is deleted, all its associated phone_numbers rows will automatically be deleted.

To achieve the same thing in Doctrine, to get the same DB-level "ON DELETE CASCADE" behavoir, you configure the @JoinColumn with the onDelete="CASCADE" option.

you will see that the same SQL will be generated as in the first, raw-SQL example

Note
Rectangle 27 0

php On delete cascade with doctrine2?


# doctrine orm:schema-tool:create --dump-sql
<?php
namespace Entities;

use Doctrine\Common\Collections\ArrayCollection;

/**
 * @Entity
 * @Table(name="contacts")
 */
class Contact 
{

    /**
     *  @Id
     *  @Column(type="integer", name="contact_id") 
     *  @GeneratedValue
     */
    protected $id;  

    /** 
     * @Column(type="string", length="75", unique="true") 
     */ 
    protected $name; 

    /** 
     * @OneToMany(targetEntity="Phonenumber", mappedBy="contact")
     */ 
    protected $phonenumbers; 

    public function __construct($name=null)
    {
        $this->phonenumbers = new ArrayCollection();

        if (!is_null($name)) {

            $this->name = $name;
        }
    }

    public function getId()
    {
        return $this->id;
    }

    public function setName($name)
    {
        $this->name = $name;
    }

    public function addPhonenumber(Phonenumber $p)
    {
        if (!$this->phonenumbers->contains($p)) {

            $this->phonenumbers[] = $p;
            $p->setContact($this);
        }
    }

    public function removePhonenumber(Phonenumber $p)
    {
        $this->phonenumbers->remove($p);
    }
}

<?php
namespace Entities;

/**
 * @Entity
 * @Table(name="phonenumbers")
 */
class Phonenumber 
{

    /**
    * @Id
    * @Column(type="integer", name="phone_id") 
    * @GeneratedValue
    */
    protected $id; 

    /**
     * @Column(type="string", length="10", unique="true") 
     */  
    protected $number;

    /** 
     * @ManyToOne(targetEntity="Contact", inversedBy="phonenumbers")
     * @JoinColumn(name="contact_id", referencedColumnName="contact_id", onDelete="CASCADE")
     */ 
    protected $contact; 

    public function __construct($number=null)
    {
        if (!is_null($number)) {

            $this->number = $number;
        }
    }

    public function setPhonenumber($number)
    {
        $this->number = $number;
    }

    public function setContact(Contact $c)
    {
        $this->contact = $c;
    }
} 
?>

<?php

$em = \Doctrine\ORM\EntityManager::create($connectionOptions, $config);

$contact = new Contact("John Doe"); 

$phone1 = new Phonenumber("8173333333");
$phone2 = new Phonenumber("8174444444");
$em->persist($phone1);
$em->persist($phone2);
$contact->addPhonenumber($phone1); 
$contact->addPhonenumber($phone2); 

$em->persist($contact);
try {

    $em->flush();
} catch(Exception $e) {

    $m = $e->getMessage();
    echo $m . "<br />\n";
}
CREATE TABLE contacts
 (contact_id BIGINT AUTO_INCREMENT NOT NULL,
 name VARCHAR(75) NOT NULL,
 PRIMARY KEY(contact_id)) ENGINE = InnoDB;

CREATE TABLE phone_numbers
 (phone_id BIGINT AUTO_INCREMENT NOT NULL,
  phone_number CHAR(10) NOT NULL,
 contact_id BIGINT NOT NULL,
 PRIMARY KEY(phone_id),
 UNIQUE(phone_number)) ENGINE = InnoDB;

ALTER TABLE phone_numbers ADD FOREIGN KEY (contact_id) REFERENCES \
contacts(contact_id) ) ON DELETE CASCADE;
DELETE TABLE contacts as c WHERE c.id=1; /* delete cascades to phone_numbers */
INSERT INTO table contacts(name) VALUES('Robert Smith');
INSERT INTO table phone_numbers(phone_number, contact_id) VALUES('8963333333', 1);
INSERT INTO table phone_numbers(phone_number, contact_id) VALUES('8964444444', 1);

@przemi_li the onDelete="cascade" is placed correctly in the entity (on the child) because that is SQL cascading, which is placed on the child. Only the Doctrine cascading (cascade=["remove"], which is not used here) is placed on the parent.

@przemo_li It is correct placement. The contact doesn't know phone numbers exist, because the phone numbers have a reference to the contact, and a contact doesn't have a reference to the phone numbers. So if a contact gets deleted, a phone number has a reference to a non-existing contact. In this case, we want something to happen: triggering the ON DELETE action. We decided to cascade the deletion, so to delete the phone numbers as well.

By adding "ON DELETE CASCADE" to the foreign key constraint, phone_numbers will automatically be deleted when their associated contact is deleted.

Here is simple example. A contact has one to many associated phone numbers. When a contact is deleted, I want all its associated phone numbers to also be deleted, so I use ON DELETE CASCADE. The one-to-many/many-to-one relationship is implemented with by the foreign key in the phone_numbers.

Now when a row in the contacts table is deleted, all its associated phone_numbers rows will automatically be deleted.

To achieve the same thing in Doctrine, to get the same DB-level "ON DELETE CASCADE" behavoir, you configure the @JoinColumn with the onDelete="CASCADE" option.

you will see that the same SQL will be generated as in the first, raw-SQL example

Note
Rectangle 27 0

php On delete cascade with doctrine2?


@ORM\JoinColumn(name="father_id", referencedColumnName="id", onDelete="CASCADE")

1) ORM level - uses cascade={"remove"} in the association - this is a calculation that is done in the UnitOfWork and does not affect the database structure. When you remove an object, the UnitOfWork will iterate over all objects in the association and remove them.

2) Database level - uses onDelete="CASCADE" on the association's joinColumn - this will add On Delete Cascade to the foreign key column in the database:

@Michael Ridgway sometimes both statements should be applied - onDelete as well as cascade = {"remove"} for example when You have some object related with fosUser. Both objects should not exist alone

@dVaffection That's a good question. I think that the onDelete="CASCADE" won't have any affect since Doctrine's cascade={"remove"} removes the related entities before removing the root entity (it has to). So when the root entity is deleted there aren't any foreign relations left for onDelete="CASCADE" to delete. But to be sure I would suggest you simply create a small test case and look at the queries being executed and their order of execution.

I also want to point out that the way you have your cascade={"remove"} right now, if you delete a Child object, this cascade will remove the Parent object. Clearly not what you want.

I do too but it depends. Say for example you have an image gallery with images. When you delete the gallery you want the images to be deleted from disk too. If you implement that in the delete() method of your image object the cascading delete using the ORM will make sure that all your image's delte() functions are called, saving you the work of implementing cronjobs that check for orphaned image files.

I generally use onDelete="CASCADE" because it means the ORM has to do less work and it should have a little bit better performance.

Note that you can just write @ORM\JoinColumn(onDelete="CASCADE") and still let doctrine handle the column names automatically.

There are two kinds of cascades in Doctrine:

Note
Rectangle 27 0

php On delete cascade with doctrine2?


@ORM\JoinColumn(name="father_id", referencedColumnName="id", onDelete="CASCADE")

1) ORM level - uses cascade={"remove"} in the association - this is a calculation that is done in the UnitOfWork and does not affect the database structure. When you remove an object, the UnitOfWork will iterate over all objects in the association and remove them.

2) Database level - uses onDelete="CASCADE" on the association's joinColumn - this will add On Delete Cascade to the foreign key column in the database:

@Michael Ridgway sometimes both statements should be applied - onDelete as well as cascade = {"remove"} for example when You have some object related with fosUser. Both objects should not exist alone

I also want to point out that the way you have your cascade={"remove"} right now, if you delete a Child object, this cascade will remove the Parent object. Clearly not what you want.

I do too but it depends. Say for example you have an image gallery with images. When you delete the gallery you want the images to be deleted from disk too. If you implement that in the delete() method of your image object the cascading delete using the ORM will make sure that all your image's delte() functions are called, saving you the work of implementing cronjobs that check for orphaned image files.

I generally use onDelete="CASCADE" because it means the ORM has to do less work and it should have a little bit better performance.

Note that you can just write @ORM\JoinColumn(onDelete="CASCADE") and still let doctrine handle the column names automatically.

Thanks! I think I understand now how to do it properly. Which one of the cascade methods do you consider best practice or would you recommend?

There are two kinds of cascades in Doctrine:

Note