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 userrole 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 userid 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?