Many to many relation on same table

[bobvandevijver]

As I wanted to use a many-to-many relation in Symfony 1.4, I got the advice at StackOverflow (http://stackoverflow.com/questions/10911261/many-to-many-relation-on-same-table) to update to the newest sfPropelORMPlugin, as I did.
With the new version, Propel did recognize the many-to-many relation correctly, however, I still got an error while saving (only when a relation is made):

Unable to execute INSERT statement [INSERT INTO `item_has_item` (`ITEM_ID`) VALUES (:p0)]
[wrapped: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a 
child row: a foreign key constraint fails (`SI/item_has_item`, CONSTRAINT 
`item_has_item_FK_1` FOREIGN KEY (`parent_item_id`) REFERENCES `item` (`id`))]

My schema.yml:

propel:
  item:
    _attributes: { phpName: Item }
    id: { phpName: Id, type: INTEGER, size: '10', primaryKey: true, autoIncrement: true, required: true }
    type_id: { phpName: TypeId, type: INTEGER, size: '11', required: true, foreignTable: type, foreignReference: id, onDelete: RESTRICT, onUpdate: RESTRICT }
    owner_id: { phpName: OwnerId, type: INTEGER, size: '11', required: true, foreignTable: owner, foreignReference: id, onDelete: RESTRICT, onUpdate: RESTRICT }
    place_id: { phpName: PlaceId, type: INTEGER, size: '11', required: true, foreignTable: place, foreignReference: id, onDelete: RESTRICT, onUpdate: RESTRICT }
    picture_id: { phpName: PictureId, type: INTEGER, size: '11', required: false, foreignTable: picture, foreignReference: id, onDelete: RESTRICT, onUpdate: RESTRICT }
    supplier_id: { phpName: SupplierId, type: INTEGER, size: '11', required: false, foreignTable: supplier, foreignReference: id, onDelete: RESTRICT, onUpdate: RESTRICT }
    name: { phpName: Name, type: VARCHAR, size: '255', required: true }
    amount: { phpName: Amount, type: INTEGER, size: '11', required: true }
    wished_amount: { phpName: WishedAmount, type: INTEGER, size: '11', required: true }
    costs: { phpName: Costs, type: DECIMAL, size: '7', scale: '2', required: true }
    description: { phpName: Description, type: LONGVARCHAR, required: false }
    use_until: { phpName: UseUntil, type: DATE, required: false }
    last_used: { phpName: LastUsed, type: TIMESTAMP, required: false }
    last_updated: { phpName: LastUpdated, type: TIMESTAMP, required: false }
    _indexes: { item_FI_1: [type_id], item_FI_2: [owner_id], item_FI_3: [place_id], item_FI_4: [picture_id], item_FI_5: [supplier_id] }
  item_has_item:
    parent_item_id: { phpName: ParentItemId, class: Item, type: INTEGER, required: true, foreignTable: item, foreignAlias: ParentItem, foreignReference: id, primaryKey: true}
    item_id: { phpName: ItemId, class: Item, type: INTEGER, required: true, foreignTable: item, foreignAlias: item, foreignReference: id, primaryKey: true}
    _indexes: { item_has_item_FI_1: [parent_item_id], item_has_item_FI_2: [item_id] }

I have found, after a long extensive search, that the form generator fails to correctly understand the many-to-many relation on the same table. In the function saveItemHasItemList($con = null):

$c = new Criteria();
$c->add(ItemHasItemPeer::ITEM_ID, $this->object->getPrimaryKey());
ItemHasItemPeer::doDelete($c, $con);

$values = $this->getValue('item_has_item_list');
if (is_array($values))
    {
      foreach ($values as $value)
      {
        $obj = new ItemHasItem();
        $obj->setItemId($this->object->getPrimaryKey());
        $obj->setItemId($value);
        $obj->save();
      }
    }

As you can see, the ItemId is twice set, while the ParentItemId is not set, hence the constraint value. It should be generated as:

$c->add(ItemHasItemPeer::PARENT_ITEM_ID, $this->object->getPrimaryKey());

&&

$obj->setParentItemId($this->object->getPrimaryKey());
$obj->setItemId($value);

This solves the saving problem, however, it still does not show the already selected relations. To solve that, you should also change the updateDefaultsFromObject():

foreach ($this->object->getItemHasItemsRelatedByItemId() as $obj)

Should be:

foreach ($this->object->getItemHasItemsRelatedByParentItemId() as $obj)

Conclusion: Propel fails to correctly build the form class of a many-to-many relation on the same table.

[jaugustin]

I will look at this.

[willdurand]

@jaugustin any news?

[jaugustin]

I didn't have time to look at this issue.

[rozwell]

It seems like a Propel bug to me... Does the issue still exists with the latest sfPropelORMPlugin and Propel?

[bobvandevijver]

I wouldn't know: I've stopped using Propel ever since we've moved to Symfony 2.x and Doctrine. Since this plugin is for SF1.x which is no longer supported, I do not think it will be solved.