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

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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