DDC-551: Consider adding ability to specify additional join conditions on a @JoinTable / @JoinColumn #682

New Issue

2026-01-22T12:46:36+01:00

admin commented

2026-01-22 12:46:36 +01:00

Originally created by @doctrinebot on GitHub (Apr 28, 2010).

Originally assigned to: @asm89 on GitHub.

Jira issue originally created by user mjh_ca:

Per discussion with beberlei and romanb in #doctrine-dev yesterday, opening this ticket as a "feature request" to support migrating legacy schemas with a special many-to-many mapping to Doctrine.

Consider the following schema:

CREATE TABLE categories (
    category*id BIGINT UNSIGNED NOT NULL AUTO*INCREMENT,
    content_type ENUM('posts', 'videos'),
    /** ... **/
    PRIMARY KEY (category_id)
) ENGINE=InnoDB;

CREATE TABLE content*category*association (
    content_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    content_type ENUM('posts', 'videos'),
    PRIMARY KEY (content*id, category_id, content*type),
    FOREIGN KEY (category*id, content_type) REFERENCES categories(category_id, content*type) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE posts (
    post*id BIGINT UNSIGNED NOT NULL AUTO*INCREMENT,
    /** ... **/
    PRIMARY KEY (post_id)
) ENGINE=InnoDB;

CREATE TABLE videos (
    video*id BIGINT UNSIGNED NOT NULL AUTO*INCREMENT,
    /** ... **/
) ENGINE=InnoDB;

There is a Many-To-Many relationship between each of the posts and videos table (via the content_category_association table) to the categories table. The difference from a standard many-to-many relationship is there is an extra column in the association table (content_type) which must be included in the join condition to return correct results. Since both the videos and posts table have their own autonumber primary keys, a join against the association table must include an extra condition (i.e. INNER JOIN ... ON ... AND content_category_association.content_type = 'posts').

Perhaps you could allow passing of additional properties to @JoinTable / joinColumns to specify the additional join condition .. i.e.:

/*** @Entity **/
class Video
{
  /****
   * @ManyToMany(targetEntity="Category")
   * @JoinTable(name="content*category*association",
   *      joinColumns={@JoinColumn(name="content*id", referencedColumnName="video*id")},
   *      inverseJoinColumns={@JoinColumn(name="category*id", referencedColumnName="video*id")},
   *      extraJoinTerms={@JoinTerm(content_type="video")}
   *      )
   */
  private $categories;

  // ...
}

/*** @Entity **/
class Category
{
    // ...
}

Certainly this schema is not ideal from a pure OO perspective. Class inheritance with a discriminator column may have been a better way to do this, thereby allowing a globally unique "content_id" for all types of content, negating the need for the extra column in the association table. However, it would nonetheless be helpful to have this additional capability within Doctrine to avoid having to re-factor such a legacy schema.

Originally created by @doctrinebot on GitHub (Apr 28, 2010). Originally assigned to: @asm89 on GitHub. Jira issue originally created by user mjh_ca: Per discussion with beberlei and romanb in #doctrine-dev yesterday, opening this ticket as a "feature request" to support migrating legacy schemas with a special many-to-many mapping to Doctrine. Consider the following schema: ``` CREATE TABLE categories ( category*id BIGINT UNSIGNED NOT NULL AUTO*INCREMENT, content_type ENUM('posts', 'videos'), /** ... **/ PRIMARY KEY (category_id) ) ENGINE=InnoDB; CREATE TABLE content*category*association ( content_id BIGINT UNSIGNED NOT NULL, category_id BIGINT UNSIGNED NOT NULL, content_type ENUM('posts', 'videos'), PRIMARY KEY (content*id, category_id, content*type), FOREIGN KEY (category*id, content_type) REFERENCES categories(category_id, content*type) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; CREATE TABLE posts ( post*id BIGINT UNSIGNED NOT NULL AUTO*INCREMENT, /** ... **/ PRIMARY KEY (post_id) ) ENGINE=InnoDB; CREATE TABLE videos ( video*id BIGINT UNSIGNED NOT NULL AUTO*INCREMENT, /** ... **/ ) ENGINE=InnoDB; ``` There is a Many-To-Many relationship between each of the posts and videos table (via the content_category_association table) to the categories table. The difference from a standard many-to-many relationship is there is an extra column in the association table (content_type) which must be included in the join condition to return correct results. Since both the videos and posts table have their own autonumber primary keys, a join against the association table must include an extra condition (i.e. INNER JOIN ... ON ... AND content_category_association.content_type = 'posts'). Perhaps you could allow passing of additional properties to @JoinTable / joinColumns to specify the additional join condition .. i.e.: ``` /*** @Entity **/ class Video { /**** * @ManyToMany(targetEntity="Category") * @JoinTable(name="content*category*association", * joinColumns={@JoinColumn(name="content*id", referencedColumnName="video*id")}, * inverseJoinColumns={@JoinColumn(name="category*id", referencedColumnName="video*id")}, * extraJoinTerms={@JoinTerm(content_type="video")} * ) */ private $categories; // ... } /*** @Entity **/ class Category { // ... } ``` Certainly this schema is not ideal from a pure OO perspective. Class inheritance with a discriminator column may have been a better way to do this, thereby allowing a globally unique "content_id" for all types of content, negating the need for the extra column in the association table. However, it would nonetheless be helpful to have this additional capability within Doctrine to avoid having to re-factor such a legacy schema.

admin added the Improvement label 2026-01-22 12:46:36 +01:00

admin closed this issue

2026-01-22 12:46:36 +01:00

admin commented

2026-01-22 12:46:37 +01:00

@doctrinebot commented on GitHub (Apr 28, 2010):

is duplicated by DDC-1007: Add ability to filter a association
is duplicated by DDC-750: Named scopes (conditional columns and associations)
is duplicated by DDC-1092: CustomTreeWalkers For Entities
is duplicated by DDC-1083: Support for a @Filter annotation to collections

@doctrinebot commented on GitHub (Apr 28, 2010): - is duplicated by [DDC-1007: Add ability to filter a association](http://www.doctrine-project.org/jira/browse/DDC-1007) - is duplicated by [DDC-750: Named scopes (conditional columns and associations)](http://www.doctrine-project.org/jira/browse/DDC-750) - is duplicated by [DDC-1092: CustomTreeWalkers For Entities](http://www.doctrine-project.org/jira/browse/DDC-1092) - is duplicated by [DDC-1083: Support for a @Filter annotation to collections](http://www.doctrine-project.org/jira/browse/DDC-1083)

admin commented

2026-01-22 12:46:39 +01:00

@doctrinebot commented on GitHub (Nov 25, 2010):

Comment created by yaroslav:

Would be great to get this functionality

@doctrinebot commented on GitHub (Nov 25, 2010): Comment created by yaroslav: Would be great to get this functionality

admin commented

2026-01-22 12:46:39 +01:00

@doctrinebot commented on GitHub (Jul 22, 2011):

Comment created by @beberlei:

Assigned to asm89

@doctrinebot commented on GitHub (Jul 22, 2011): Comment created by @beberlei: Assigned to asm89

admin commented

2026-01-22 12:46:39 +01:00

@doctrinebot commented on GitHub (Jul 22, 2011):

Comment created by @beberlei:

Scheduled for 2.2

@doctrinebot commented on GitHub (Jul 22, 2011): Comment created by @beberlei: Scheduled for 2.2

admin commented

2026-01-22 12:46:41 +01:00

@doctrinebot commented on GitHub (Aug 16, 2011):

Comment created by @asm89:

I've been working on this ticket over here:
https://github.com/asm89/doctrine2/tree/DDC-551

Latest thing I added was the state of the collection of filters, because this is needed for parsing (and sometimes not parsing) the queries to generate SQL. I'd like some feedback about the state keeping. More information at the commit:
https://github.com/asm89/doctrine2/commit/2653d735e230d19b0fcb703800008f3abdb7a3ba

At this point the EntityManager keeps track of this state, but maybe it would be nice to have a separate FilterCollection keep track of the state/hashes etc?

@doctrinebot commented on GitHub (Aug 16, 2011): Comment created by @asm89: I've been working on this ticket over here: https://github.com/asm89/doctrine2/tree/[DDC-551](http://www.doctrine-project.org/jira/browse/DDC-551) Latest thing I added was the state of the collection of filters, because this is needed for parsing (and sometimes not parsing) the queries to generate SQL. I'd like some feedback about the state keeping. More information at the commit: https://github.com/asm89/doctrine2/commit/2653d735e230d19b0fcb703800008f3abdb7a3ba At this point the EntityManager keeps track of this state, but maybe it would be nice to have a separate FilterCollection keep track of the state/hashes etc?

admin commented

2026-01-22 12:46:41 +01:00