Symfony2 embedded forms - updating collection

[jkabat]

In many-to-many relationship, changing a part of the primary key generates wrong SQL sequence.

When I do DELETE, and ADD NEW, everything works fine - DELETE and INSERT sql commands are issued.

When I do UPDATE of existing collection item, DELETE and UPDATE sql commands are issued, which obviously leads to lost reference.

Example (using dropdown I have changed attribute from 30 to 9):

DELETE
FROM `ProductAttribute`
WHERE (ProductAttribute.productId=304 AND ProductAttribute.attributeId=30);
...
UPDATE `ProductAttribute` SET `attributeId`=9, `value`='aa'
WHERE ProductAttribute.productId=304 AND ProductAttribute.attributeId=9;

Attribute 9 is missing before these queries are run, so I end up with missing reference. Is there any chance of this being fixed?

Any other solution besides DELETE and ADD NEW?

[stood]

Plz join your schema.xml

2014-07-08 12:31 GMT+02:00 JK [email protected]:

In many-to-many relationship, changing a part of the primary key generates
wrong SQL sequence.

When I do DELETE, and ADD NEW, everything works fine - DELETE and INSERT
sql commands are issued.

When I do UPDATE of existing collection item, DELETE and UPDATE sql
commands are issued, which obviously leads to lost reference.

Example (using dropdown I have changed attribute from 30 to 9):

DELETE
FROM ProductAttribute
WHERE (ProductAttribute.productId=304 AND ProductAttribute.attributeId=30);
...
UPDATE ProductAttribute SET attributeId=9, value='aa'
WHERE ProductAttribute.productId=304 AND ProductAttribute.attributeId=9;

Attribute 9 is missing before these queries are run, so I end up with
missing reference. Is there any chance of this being fixed?

Any other solution besides DELETE and ADD NEW?

—
Reply to this email directly or view it on GitHub
#280.

[jkabat]

Here it goes:

    <table name="ProductAttribute" phpName="ProductAttribute" idMethod="native" isCrossRef="true">
        <column name="productId" phpName="ProductId" type="SMALLINT" size="5" sqlType="smallint(5) unsigned" primaryKey="true" required="true"/>
        <column name="attributeId" phpName="AttributeId" type="TINYINT" size="3" sqlType="tinyint(3) unsigned" primaryKey="true" required="true"/>
        <column name="value" phpName="Value" type="VARCHAR" size="20" required="true"/>
        <column name="onListing" phpName="OnListing" type="BOOLEAN" size="1" required="false" defaultValue="false"/>
        <column name="ordering" phpName="Ordering" type="TINYINT" size="3" sqlType="tinyint(3) unsigned" required="true"/>
        <foreign-key foreignTable="Product" name="productattribute_ibfk_1" onDelete="CASCADE" onUpdate="RESTRICT">
            <reference local="productId" foreign="id"/>
        </foreign-key>
        <foreign-key foreignTable="Attribute" name="productattribute_ibfk_2" onDelete="CASCADE" onUpdate="RESTRICT">
            <reference local="attributeId" foreign="id"/>
        </foreign-key>
        <index name="onListing">
            <index-column name="onListing"/>
        </index>
        <index name="ordering">
            <index-column name="productId"/>
            <index-column name="ordering"/>
        </index>
        <vendor type="mysql">
            <parameter name="Engine" value="InnoDB"/>
        </vendor>
    </table>

[jkabat]

May onUpdate="RESTRICT" be responsible for such a behavior?

EDIT: seems like RESTRICT is there by default and shouldn't caused it.

[jkabat]

This issue happens only when part of composite key is changed. Updating of other columns causes no problem at all.