Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-551

Consider adding ability to specify additional join conditions on a @JoinTable / @JoinColumn

    Details

    • Type: Improvement Improvement
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 2.0-BETA1
    • Fix Version/s: 2.2
    • Component/s: ORM
    • Security Level: All
    • Labels:
      None

      Description

      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.

        Issue Links

          Activity

          Hide
          Benjamin Eberlei added a comment -

          Related Pull Request was closed: https://github.com/doctrine/doctrine2/pull/225

          Show
          Benjamin Eberlei added a comment - Related Pull Request was closed: https://github.com/doctrine/doctrine2/pull/225
          Hide
          Benjamin Eberlei added a comment -

          This issue is referenced in Github Pull-Request GH-227
          https://github.com/doctrine/doctrine2/pull/227

          Show
          Benjamin Eberlei added a comment - This issue is referenced in Github Pull-Request GH-227 https://github.com/doctrine/doctrine2/pull/227
          Hide
          Benjamin Eberlei added a comment -

          Related Pull Request was closed: https://github.com/doctrine/doctrine2/pull/227

          Show
          Benjamin Eberlei added a comment - Related Pull Request was closed: https://github.com/doctrine/doctrine2/pull/227
          Hide
          Glen Ainscow added a comment -

          Alex mentioned on IRC that filters do not provide the functionality that the OP requires, so this issue should really re-opened, unless I'm missing something?

          Show
          Glen Ainscow added a comment - Alex mentioned on IRC that filters do not provide the functionality that the OP requires, so this issue should really re-opened, unless I'm missing something?
          Hide
          Benjamin Eberlei added a comment -

          This issue is referenced in Github Pull-Request GH-237
          https://github.com/doctrine/doctrine2/pull/237

          Show
          Benjamin Eberlei added a comment - This issue is referenced in Github Pull-Request GH-237 https://github.com/doctrine/doctrine2/pull/237

            People

            • Assignee:
              Alexander
              Reporter:
              Marc Hodgins
            • Votes:
              5 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: