21/05/2015 - DOCTRINE, SYMFONY
Example below shows one-to-many association between three entities. "Country" (1) to (n) "League" (1) to (n) "Team". For more info about removing entities with cascade options, click here.
cascade={"remove"} in the parent association. It doesn't change database structure. When removing a parent object, UnitOfWork will iterate over all objects in the association and remove them (child object). e.g. if a Country has 100 associated Leagues then that means 100 DELETE queries will be run which can be very costly.onDelete="CASCADE" on the child association's joinColumn. It changes database structure by adding "On Delete Cascade" to the foreign key column in the database. It forces the database to remove all associated objects internally so it is fast. e.g. if a Country has 100 associated Leagues then that means 100 DELETE queries will be run which can be very costly only if cascade={"remove"} is set on parent table otherwise it will only run a single DELETE to delete all in one go./**
* @ORM\Table(name="country")
*/
class Country
{
/**
* @ORM\OneToMany(
* targetEntity="League",
* mappedBy="country",
* cascade={"persist", "remove"}
* )
*/
protected $league;
}
/**
* @ORM\Table(name="league")
*/
class League
{
/**
* @ORM\ManyToOne(
* targetEntity="Country",
* inversedBy="league"
* )
* @ORM\JoinColumn(
* name="country_id",
* referencedColumnName="id",
* onDelete="CASCADE",
* nullable=false
* )
*/
protected $country;
/**
* @ORM\OneToMany(
* targetEntity="Team",
* mappedBy="league",
* cascade={"persist", "remove"}
* )
*/
protected $team;
}
/**
* @ORM\Table(name="team")
*/
class Team
{
/**
* @ORM\ManyToOne(
* targetEntity="League",
* inversedBy="team"
* )
* @ORM\JoinColumn(
* name="league_id",
* referencedColumnName="id",
* onDelete="CASCADE",
* nullable=false
* )
*/
protected $league;
}
CREATE TABLE `country` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
`code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_5373C9665E237E06` (`name`),
UNIQUE KEY `UNIQ_5373C96677153098` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=141 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `league` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`country_id` smallint(6) NOT NULL,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNIQ_3EB4C3185E237E06F92F3E70` (`name`,`country_id`),
KEY `IDX_3EB4C318F92F3E70` (`country_id`),
CONSTRAINT `FK_3EB4C318F92F3E70` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=205 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `team` (
`id` smallint(6) NOT NULL AUTO_INCREMENT,
`league_id` smallint(6) NOT NULL,
`name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `IDX_C4E0A61F58AFC4DE` (`league_id`),
CONSTRAINT `FK_C4E0A61F58AFC4DE` FOREIGN KEY (`league_id`) REFERENCES `league` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
mysql> SELECT * FROM country;
+-----+---------+---------------------+------------+------+
| id | name | created_at | updated_at | code |
+-----+---------+---------------------+------------+------+
| 138 | Germany | 2015-05-22 21:20:04 | NULL | DE |
| 139 | Spain | 2015-05-22 21:20:04 | NULL | ES |
| 140 | Turkey | 2015-05-22 21:20:04 | NULL | TR |
+-----+---------+---------------------+------------+------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM league;
+-----+------------+-------------------+---------------------+------------+
| id | country_id | name | created_at | updated_at |
+-----+------------+-------------------+---------------------+------------+
| 199 | 138 | Bundesliga | 2015-05-22 21:20:04 | NULL |
| 200 | 138 | 2. Bundesliga | 2015-05-22 21:20:04 | NULL |
| 201 | 139 | Primera División | 2015-05-22 21:20:04 | NULL |
| 202 | 139 | Segunda División | 2015-05-22 21:20:04 | NULL |
| 203 | 140 | Süper Lig | 2015-05-22 21:20:04 | NULL |
| 204 | 140 | TFF 1. Lig | 2015-05-22 21:20:04 | NULL |
+-----+------------+-------------------+---------------------+------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM team;
+----+-----------+------------------+
| id | league_id | name |
+----+-----------+------------------+
| 59 | 199 | FC Augsburg |
| 60 | 199 | Bayer Leverkusen |
+----+-----------+------------------+
2 rows in set (0.00 sec)
Tests results below depend on dummy data above.
If we delete Germany from Country, ORM would issue 5 delete queries. 1 for Country, 2 for League, 2 for Team. ORM seems to be doing all the work. This is slow.
Parent entity:@ORM\OneToMany(cascade={"remove"})
Child entity:@ORM\JoinColumn(onDelete="CASCADE")
If we delete Germany from Country, ORM would issue 5 delete queries. 1 for Country, 2 for League, 2 for Team. ORM seems to be doing all the work. This is slow.
Parent entity:@ORM\OneToMany(cascade={"remove"})
Child entity:@ORM\JoinColumn()
If we delete Germany from Country, ORM would issue 1 delete query which is for Country. In this case database does all the work by removing associated records from other tables so it issues delete queries in database level. 2 for League, 2 for Team. This is fast.
Parent entity:@ORM\OneToMany()
Child entity:@ORM\JoinColumn(onDelete="CASCADE")
If we try to delete a parent record it would obviously throw "SQLSTATE[23000]: Integrity constraint violation" exception so this is not what we want.
Parent entity:@ORM\OneToMany()
Child entity:@ORM\JoinColumn()