In symfony/doctrine's schema.yml, where should I put onDelete: CASCADE for a many-to-many relationship?

I have a many-to-many relationship defined in my Symfony (using doctrine) project between Orders and Upgrades (an Order can be associated with zero or more Upgrades, and an Upgrade can apply to zero or more Orders).

# schema.yml Order: columns: order_id: {...} relations: Upgrades: class: Upgrade local: order_id foreign: upgrade_id refClass: OrderUpgrade Upgrade: columns: upgrade_id: {...} relations: Orders: class: Order local: upgrade_id foreign: order_id refClass: OrderUpgrade OrderUpgrade: columns: order_id: {...} upgrade_id: {...}

I want to set up delete cascade behavior so that if I delete an Order or an Upgrade, all of the related OrderUpgrades are deleted. Where do I put onDelete: CASCADE? Usually I would put it at the end of the relations section, but that would seem to imply in this case that deleting Orders would cascade to delete Upgrades. Is Symfony + Doctrine smart enough to know what I'm wanting if I put onDelete: CASCADE in the above relations sections of schema.yml?

-------------Problems Reply------------

After much trial and error, the only way I was able to get it to work was to follow the suggestion in Jestep's comment and move the relationship definitions including onDelete: CASCADE to the linking table, so in the end it looks like this and behaves how I want it to (deletes cascade from Order to OrderUpgrade, and from Upgrade to OrderUpgrade):

# schema.yml

Order:
columns:
order_id: {...}

Upgrade:
columns:
upgrade_id: {...}

OrderUpgrade:
columns:
order_id: { type: integer, notnull: true, primary: true }
upgrade_id: { type: integer, notnull: true, primary: true }
relations:
Order:
onDelete: CASCADE
Upgrade:
onDelete: CASCADE

I must say I'm got a bit overwhelmed with all of the different Doctrine Many-to-Many YML examples out there on the internet, each with the relations in slightly different places. A frustrating experience.

I almost always use Propel but it should be essentially the same. Use: onDelete: CASCADE

Should be:

Order:
columns:
order_id: {...}
relations:
Upgrades:
onDelete: CASCADE
class: Upgrade
local: order_id
foreign: upgrade_id
refClass: OrderUpgrade

I got tired of googling about it everytime I need cascading on many to many relations and finding incomplete answers so here is my take on it which so far is the most complete available.

After a simple test, I conclude you should define the relations in all 3 entities.
- Doing it just in the 2 entities you want to join works well except for the cascading part.
- Doing it just in the join table (OrderUpgrade) works but you wont get the form and formfilter code generated in your 2 entities.
- Doing it in all 3 entities will give you both worlds.

In the following example, which is verbose but I prefer it like that:
- if you delete a Property, the corresponding PropertyLandlord entry is deleted, and the Landlord entry is untouched.
- if you delete a Landlord, same thing.
- and if you delete a PropertyLandlord record, it only removes the link entry and leaves untouched which I guess is the behaviour we would mostly be looking for.

Property:
columns:
id:
type: integer(8)
primary: true
autoincrement: true
title:
type: string(255)
notnull: true
relations:
Landlords:
class: Landlord
local: property_id
foreign: landlord_id
refClass: PropertyLandlord

Landlord:
columns:
id:
type: integer(8)
primary: true
autoincrement: true
title:
type: string(255)
notnull: true
relations:
Properties:
class: Property
local: landlord_id
foreign: property_id
refClass: PropertyLandlord
onDelete: CASCADE

PropertyLandlord:
columns:
property_id:
type: integer(8)
primary: true
notnull: true
landlord_id:
type: integer(8)
primary: true
notnull: true
relations:
Property:
local: property_id
foreign: id
class: Property
onDelete: CASCADE
Landlord:
local: landlord_id
foreign: id
class: Landlord
onDelete: CASCADE

Category:database design Views:2 Time:2009-09-28

Related post

  • how to resolve symfony doctrine:build-schema error (Unknown relation alias table_name) 2009-10-26

    how to resolve this symfony error : C:\inetpub\wwwroot\project\trunk\preprod\signup>php symfony doctrine:build-schema --trace >> doctrine generating yaml schema from database [sfException] Unknown relation alias table_name Exception trace: a

  • Symfony 1.4/doctrine/sfGuard schema.yml Using sfDoctringGuardPlugin's sfGuardGroup table in a many to many relation 2011-08-08

    am using the sfGuardPlugin and especially the sfGuardGroup table that am using in a many-to-many relation with a "monitor" table (the many to many table is called ALERT): here is my schema.yml: Monitor: tableName: monitor actAs: Timestampable: ~ colu

  • Symfony Doctrine Database Schema relation to different database 2011-07-26

    I'm having this problem about designing doctrine database schema. Assuming I have 2 databases, A and B. I've already make database A schema, and now I need to make database B schema. In database B, one of the tables has relation to database A's table

  • symfony 1.4.8+ doctrine --> doctrine:build-schema fatal error opening required /tmp/tmp_doctrine/User.php 2010-10-16

    I'm trying out symfony so i started installing it on a remote mashine thru ssh (normal user, no root access) when getting to the part where i want to generate the db-schema out of a existing mysql database with doctrine by issuing the following comma

  • How does doctrine:build-schema create config/doctrine/schema.yml? 2010-11-19

    I'm having a really tough problem and, in an effort to solve it, I want to find the code that's generating config/doctrine/schema.yml. I'm having a hard time tracking it down. Would anyone happen to know where it is? --------------Solutions----------

  • In symfony 1.4, is there a way to override or extend parts of a generated schema.yml? 2011-04-11

    We are using symfony 1.4 with doctrine. Because of the nature of our development, the three of us working on this project need to regenerate our schema, model, forms and filters every morning. I want to keep a table set as "versionable" in the schema

  • doctrine:build-schema ignoring unique key 2011-02-22

    i am using the symfony command to generate my schema.yml file from my mysql 5.1 database by running: symfony doctrine:build-schema My table: CREATE TABLE `identity` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `username` VARCHAR(64) DEFAULT NULL,

  • symfony doctrine build-sql error 2010-04-10

    I have some big problems with symfony and doctrine at the beginning of a new project. I have created database diagram with mysql workbench, inserted the sql into phpmyadmin and then I've tried symfony doctrine:build-schema to generate the YAML schema

  • "Missing class name" error on doctrine:build-schema 2010-12-02

    I have a project that connects to multiple databases. When I run doctrine:build-schema, I get an error that simply says "Missing class name." Any idea what could be going on? I suspect it has something to do with the fact that I'm connecting to multi

  • doctrine:build-schema error 2011-02-04

    When I am trying to run symfony doctrine:build-schema command to fetch the schema structure to the yaml file, I got the error: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "t" LINE 6: ... t.typtype ... ^. Failing Que

  • How to define doctrine record filter in symfony's schema.yml? 2011-02-01

    Doctrine documentation explains that in order to add doctrine record filter, you have to unshiftFilter() to table definition setUp() method: public function setUp(){ parent::setUp(); $this->unshiftFilter(new MyDoctrineRecordFilter()); } In symfony

  • Symfony, Doctrine, schema.yml, -guessing- relations problem 2011-02-02

    I prepared a schema.yml -you can find below-. When i build it, build sql and inserting sql; i haven't face any problem. But when i try to load data (fixtures) it gives me an error -you can find below-. I can't find any problem on my schema and especi

  • Symfony 1.4/doctrine schema.yml many to many relations does'nt work properly? 2011-08-05

    I have a little disturbing problem in here! an using symfony 1.4 with Doctrine! i fact i have a "many to many" relation (see code bellow) but i don't have the RIGHT result! Monitor: actAs: Timestampable: ~ columns: label: {type: string(45)} url: {typ

  • How can I orderby in schema.yml doctrine symfony 1.4 2012-03-13

    I've got a many to many relationship from Project to Asset using ProjectAsset class as middle class between this 2. When I use getProjectAsset() method on a project it returns all my ProjectAssets assigned to that project. How can I change the schema

  • Export MySQL Workbench data model directly to Schema YML in Propel/ Symfony 2009-03-01

    Is there any plugin that directly exports MYSQL Workbench data model directly to YML for Propel consumption? --------------Solutions------------- Yes, MySQL Workbench Plugins are available for generating schemas for Propel, Doctrine, Symfony, etc htt

  • Doctrine schema.yml generator 2009-11-23

    I am pretty new to doctrine. I made two small projects with doctrine for my own but now I am about to create big project for my client. The project will have more than 50 tables. Is there any way of generating schema.yml? I tried DB Designer and conv

  • Schema in Symfony/Doctrine 2010-10-03

    # config/doctrine/schema.yml Category: actAs: { Timestampable: ~ } columns: name: { type: string(255), notnull: true, unique: true } Project: actAs: { Timestampable: ~ } columns: category_id: { type: integer, notnull: true } title: { type: string(255

  • Retrieve database names / dsn info from symfony / doctrine databases.yml 2011-09-11

    I have a complex data structure that means I have not been able to fully use relationships defined in schema.yml for my model. Now I have some reporting queries which need to use tables from several databases. Because of the complexity of the model t

  • Setting up first schema.yml for Symfony 1.4 2012-02-11

    I'm trying to set up Symfony 1.4. I'm not new to MVC but this is my first attempt at Symfony. I've been following the documentation on symfony-project.org/reference/1_4 and I have a "Hello, World!" page. I moved on to the next step -- database. I did

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.183 (s). 11 q(s)