0 votes

When you define a many-to-many relation between two tables in ORM Designer it creates
problems with generated Propel code.

For an example I created a simple schema with an user, role and userrole table.
Then I create a many-to-many from user to role with user
role as the many-to-many table.
ORM Designer generates the code below. The problem here is that the order of fields
userid and roleid in the user_table entity is different from the role and user many-to-many-entity order
in many-to-many.

Field order in entity is iduser and then idrole. But in many-to-many it is role and then user.

<?xml version="1.0"?>
<orm-designer version="2.2.5.754" name="example" mvc="Without MVC" orm="Propel" uuid="e8db9281-168f-4053-81cf-3655acc8c9f3">
  <module name="MainModule" uuid="abaa11d3-1bf5-4609-a9f6-b685e0696cc7">
    <entity name="role" uuid="971c5856-964e-4a25-8041-7447f8116792">
      <field name="id" type="Integer" required="true" unique="true" primary="true" auto-increment="true" uuid="ab879bd5-1ee8-4efa-b0d8-48a906677908"/>
      <field name="name" type="Varchar" size="255" required="true" uuid="6b931444-f57d-4972-9ee8-af9bec06beb5"/>
    </entity>
    <entity name="user_role" uuid="51b5fa5b-ce8c-43e9-b97d-608cf89ad385">
      <field name="user_id" type="integer" required="true" primary="true" uuid="54e0e6f8-7e85-4af9-952f-0363d65bae2b"/>
      <field name="role_id" type="Integer" required="true" primary="true" uuid="150507d2-9eb4-4bd9-a947-5c33666cbc90"/>
    </entity>
    <many-to-many mn-entity="user_role" caption="New ManyToMany" uuid="5899d90c-9262-4007-8321-730b9eee7e8d">
      <many-to-many-entity name="role" owning-side="true" uuid="4ac78eba-27b3-40fa-b56d-896bef5f4e0d">
        <many-to-many-field from="role_id" to="id"/>
      </many-to-many-entity>
      <many-to-many-entity name="user" owning-side="false" uuid="560a83c4-e810-49bb-a2f3-ef87f88cb7ab">
        <many-to-many-field from="user_id" to="id"/>
      </many-to-many-entity>
    </many-to-many>
    <entity name="user" uuid="bb1cbf03-68f8-4ec7-b7a0-0124b935c3ab">
      <field name="id" type="integer" required="true" unique="true" primary="true" auto-increment="true" uuid="ab23b1d6-b167-44d9-b28d-6c62eaf63af4"/>
      <field name="username" type="Varchar" size="255" required="true" uuid="d0d8621e-e492-402b-acf9-69b18d282355"/>
    </entity>
  </module>
  <visual-data>
    <entity uuid="51b5fa5b-ce8c-43e9-b97d-608cf89ad385" position-x="192" position-y="173" size-x="0" size-x2="51" size-y="0" size-y2="20"/>
    <entity uuid="971c5856-964e-4a25-8041-7447f8116792" position-x="338" position-y="31" size-x="0" size-x2="110" size-y="0" size-y2="45"/>
    <module uuid="abaa11d3-1bf5-4609-a9f6-b685e0696cc7" bk-color="4294375930" position-x="61" position-y="72" size-x="11" size-x2="514" size-y="22" size-y2="331"/>
    <entity uuid="bb1cbf03-68f8-4ec7-b7a0-0124b935c3ab" position-x="10" position-y="20"/>
    <project uuid="e8db9281-168f-4053-81cf-3655acc8c9f3" size-x="0" size-x2="625" size-y="0" size-y2="453"/>
  </visual-data>
</orm-designer>

Now when Propel schema is exported it comes out as:

<?xml version="1.0"?>
<database name="MainModule">
  <table name="role">
    <column name="id" type="Integer" required="true" autoIncrement="true" primaryKey="true"/>
    <column name="name" type="Varchar" size="255" required="true"/>
    <unique name="IX_UQ_role_id">
      <unique-column name="id"/>
    </unique>
  </table>
  <table name="user_role" isCrossRef="true">
    <column name="user_id" type="integer" required="true" primaryKey="true"/>
    <column name="role_id" type="Integer" required="true" primaryKey="true"/>
    <foreign-key foreignTable="role">
      <reference local="role_id" foreign="id"/>
    </foreign-key>
    <foreign-key foreignTable="user">
      <reference local="user_id" foreign="id"/>
    </foreign-key>
  </table>
  <table name="user">
    <column name="id" type="integer" required="true" autoIncrement="true" primaryKey="true"/>
    <column name="username" type="Varchar" size="255" required="true"/>
    <unique name="IX_UQ_user_id">
      <unique-column name="id"/>
    </unique>
  </table>
</database>

Now you can see that in table propeluser the order of columns and foreign-keys also differ.
Colum user
id first and then role_id. But for foreign-key it is role first and then user.

This creates issues where arrays of primary keys are generated in the generated Propel code.
Because order of fields and foreign keys are switched it will reverse primary keys at runtime in
certain situations.

This is very apparent when you remove roles from the user or remove users from a role and then
save that user of role. It will reverse the field values when it deleted the user_role entries.

Can you make it so that order of foreign keys corresponds to the order of fields, especially in
the exported Propel schema?

in Solved by (230 points)
recategorized by

2 Answers

0 votes
Best answer

Hi,
This happens because Propel processes the order of the elements in the XML file, unlike other frameworks which do not require corresponding order.
We have checked the behavior and prepared a solution so exported files will conform with this convention. Then for all new created MN associations the fields of the MN entities will be ordered as required by Propel. This change will be included in next ORM Designer update.
At the moment you can fix your existing projects by arranging the MN entity field-order manually (this will change the order in the export). See screenshots below:

Thank you for letting us know about this.
Martin

by Skipper developer (74.8k points)
selected by

I already did manual fix of reordering foreign keys in the ORM Designer schema xml to correspond with the order of the columns. If after you open the schema in ORM Designer it will export the correct order when I export the schema to Propel format.

But nice to hear you found the problem and hope you can fix it.

0 votes

Hi,
thanks for contacting us.
I will have a look at this and let you know what can be done.

Martin

by Skipper developer (74.8k points)

This problem still exists. Just ran into it again after quite a long time I must admit. Have you already managed to look at this problem?

This problem still persists. Has there been done anything about it?