Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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.


Facts



Entities


/**
* @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;
}

Database structure


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;

Dummy data


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


Tests results below depend on dummy data above.


Test 1


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")

Test 2


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()

Test 3


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")

Test 4


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()