Skip to content
This repository

Many to many relation on same table #143

Open
bobvandevijver opened this Issue June 10, 2012 · 3 comments

3 participants

bobvandevijver Jérémie Augustin William Durand
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.

Jérémie Augustin
Collaborator

I will look at this.

William Durand
Owner

@jaugustin any news?

Jérémie Augustin
Collaborator

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.