Add support for conditional FK-s

[brainartdev]

I'd like to propose feature to add extra conditions for foreign keys, e.g:

<table name="object_log_entry" description="Table which stores object changes">
  <column name="object_type" type="varchar" size="50" required="true" />
  <column name="object_id" type="bigint" required="true" />
  ...

  <foreign-key foreignTable="user" onDelete="cascade" onUpdate="cascade">
    <reference local="object_type" expression="USER" />
    <reference local="object_id" foreign="id" />
  </foreign-key>
</table>

Which would create something like:

...
FROM
  user u JOIN object_log_entry ole ON ole.object_type='USER' AND ole.object_id=u.id
...

Of course, this would imply that database-level FK is not created, cascading and stuff would be done by model.

[marcj]

I'd love to see a polymorphic association support in Propel.

I believe you have a mistake in your SQL, it should be:

FROM
   object_log_entry ole
LEFT JOIN user u ON (ole.object_type='USER' AND ole.object_id=u.id)

A similar (opposite) scenario is when we want to have a relation to a subset of a foreign table.

E.g.

<table name="user">
  <foreign-key foreignTable="object_log_entry" >
    <reference value="USER" foreign="object_type" />
    <reference local="id" foreign="object_id" />
  </foreign-key>
</table>

that generates:

FROM
   user u
JOIN object_log_entry ole ON (ole.object_type='USER' AND ole.object_id=u.log_id)

@brainartdev, would you be able to provide a PR?

[marcj]

I'm not sure if the name foreign-key still fits then. Maybe <relation ..> would be better.

[unitedworx]

I guess by trying to use Single Table Inheritance I was basicly trying to emulate an extra condition for a relation, essentially for the same goal as the above suggestion of allowing us to specify extra conditions on Foreign keys.

Nice to see this being discussed and requested by others, not sure why this is not there already considering all the stuff you are able to pull with propel!