Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1318

Unexpectet behavior while using ManyToOne as part ofr composite key

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Minor Minor
    • Resolution: Incomplete
    • Affects Version/s: 2.1
    • Fix Version/s: None
    • Component/s: Mapping Drivers
    • Security Level: All
    • Labels:
      None
    • Environment:
      ubuntu 64x

      Description

      Hi!

      Ran into a problem while wanted to use a ManyToOne as part of a primary key:

       * @Entity
       * @Table(name="user_preferences")
        */
      class UserPreferences  {
      
          /**
           * @Id
           * @ManyToOne(targetEntity="User",cascade={"persist"})
           *  @JoinColumn(name="user_id", referencedColumnName="id")
           */
          protected $user;
          /**
           * @Id
           * @GeneratedValue(strategy="NONE")
           * @Column(name="preference_id",type="smallint",nullable=false)
           */
          protected $preference_id;
      

      By default doctrine creates a table with 2 single keys (the preference key is the primary key) which is of course incorrect.
      Had to add @Column(name="user_id",type="integer") to the user column to fix the index problem but that introduced another problem. The entity no longer accepter \Entity\User as a value for user and takes only a smallint as defined.

        Activity

        Peter Jasiulewicz created issue -
        Hide
        Benjamin Eberlei added a comment -

        This is probably because of the @GeneratedStrategy annotation, can you try to just remove it? For me there is always only one key generated.

        Show
        Benjamin Eberlei added a comment - This is probably because of the @GeneratedStrategy annotation, can you try to just remove it? For me there is always only one key generated.
        Hide
        Peter Jasiulewicz added a comment - - edited

        Hi,
        thanks fo the fast reply

             * @Id
             * @ManyToOne(targetEntity="User",cascade={"persist"})
             *  @JoinColumn(name="user_id", referencedColumnName="id")
             */
            protected $user;
        
             * 
             * @Id
             * @Column(name="preference_id",type="smallint",nullable=false)
             */
            protected $preference_id;
        

        On MySQL creates:

        mysql> show indexes from xxx.user_preferences;
        +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        | Table            | Non_unique | Key_name                     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
        +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        | user_preferences |          0 | PRIMARY                      |            1 | preference_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
        | user_preferences |          1 | user_preferences_user_id_idx |            1 | user_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
        +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        2 rows in set (0.01 sec)
        

        With:

             * @Id
             * @ManyToOne(targetEntity="User",cascade={"persist"})
             *  @JoinColumn(name="user_id", referencedColumnName="id")
             * @Column(name="user_id",type="integer")
             */
            protected $user;
         
             
             * @Id
             * @GeneratedValue(strategy="NONE")
             * @Column(name="preference_id",type="smallint",nullable=false)
             */
            protected $preference_id;
        

        Creates:

        mysql> show indexes from xxx.user_preferences;
        +------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        | Table            | Non_unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
        +------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        | user_preferences |          0 | PRIMARY  |            1 | user_id       | A         |           0 |     NULL | NULL   |      | BTREE      |         |
        | user_preferences |          0 | PRIMARY  |            2 | preference_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
        +------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        2 rows in set (0.00 sec)
        

        The annotations:

             * @Id
             * @ManyToOne(targetEntity="User",cascade={"persist"})
             *  @JoinColumn(name="user_id", referencedColumnName="id")
             */
            protected $user;
        
             * @Id
             * @Column(name="preference_id",type="smallint",nullable=false)
             */
            protected $preference_id;
        

        Create the same effect like the first one:

        mysql> show indexes from xxx.user_preferences;
        +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        | Table            | Non_unique | Key_name                     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
        +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        | user_preferences |          0 | PRIMARY                      |            1 | preference_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
        | user_preferences |          1 | user_preferences_user_id_idx |            1 | user_id       | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |
        +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+
        2 rows in set (0.00 sec)
        

        Sequence of events :
        1) change in annotations in entity object
        2) dropped the table affected
        3) orm:schema-tool:update --force

        Show
        Peter Jasiulewicz added a comment - - edited Hi, thanks fo the fast reply * @Id * @ManyToOne(targetEntity= "User" ,cascade={ "persist" }) * @JoinColumn(name= "user_id" , referencedColumnName= "id" ) */ protected $user; * * @Id * @Column(name= "preference_id" ,type= "smallint" ,nullable= false ) */ protected $preference_id; On MySQL creates: mysql> show indexes from xxx.user_preferences; +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | user_preferences | 0 | PRIMARY | 1 | preference_id | A | 0 | NULL | NULL | | BTREE | | | user_preferences | 1 | user_preferences_user_id_idx | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.01 sec) With: * @Id * @ManyToOne(targetEntity= "User" ,cascade={ "persist" }) * @JoinColumn(name= "user_id" , referencedColumnName= "id" ) * @Column(name= "user_id" ,type= "integer" ) */ protected $user; * @Id * @GeneratedValue(strategy= "NONE" ) * @Column(name= "preference_id" ,type= "smallint" ,nullable= false ) */ protected $preference_id; Creates: mysql> show indexes from xxx.user_preferences; +------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | user_preferences | 0 | PRIMARY | 1 | user_id | A | 0 | NULL | NULL | | BTREE | | | user_preferences | 0 | PRIMARY | 2 | preference_id | A | 0 | NULL | NULL | | BTREE | | +------------------+------------+----------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) The annotations: * @Id * @ManyToOne(targetEntity= "User" ,cascade={ "persist" }) * @JoinColumn(name= "user_id" , referencedColumnName= "id" ) */ protected $user; * @Id * @Column(name= "preference_id" ,type= "smallint" ,nullable= false ) */ protected $preference_id; Create the same effect like the first one: mysql> show indexes from xxx.user_preferences; +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ | user_preferences | 0 | PRIMARY | 1 | preference_id | A | 0 | NULL | NULL | | BTREE | | | user_preferences | 1 | user_preferences_user_id_idx | 1 | user_id | A | 0 | NULL | NULL | YES | BTREE | | +------------------+------------+------------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+ 2 rows in set (0.00 sec) Sequence of events : 1) change in annotations in entity object 2) dropped the table affected 3) orm:schema-tool:update --force
        Hide
        Benjamin Eberlei added a comment -

        Formatted code.

        Show
        Benjamin Eberlei added a comment - Formatted code.
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Description Hi!

        Ran into a problem while wanted to use a ManyToOne as part of a primary key:

         
         * @Entity
         * @Table(name="user_preferences")
          */
        class UserPreferences {

            /**
             * @Id
             * @ManyToOne(targetEntity="User",cascade={"persist"})
             * @JoinColumn(name="user_id", referencedColumnName="id")
             */
            protected $user;
            /**
             * @Id
             * @GeneratedValue(strategy="NONE")
             * @Column(name="preference_id",type="smallint",nullable=false)
             */
            protected $preference_id;


        By default doctrine creates a table with 2 single keys (the preference key is the primary key) which is of course incorrect.
        Had to add @Column(name="user_id",type="integer") to the user column to fix the index problem but that introduced another problem. The entity no longer accepter \Entity\User as a value for user and takes only a smallint as defined.
        Hi!

        Ran into a problem while wanted to use a ManyToOne as part of a primary key:

         {code}
         * @Entity
         * @Table(name="user_preferences")
          */
        class UserPreferences {

            /**
             * @Id
             * @ManyToOne(targetEntity="User",cascade={"persist"})
             * @JoinColumn(name="user_id", referencedColumnName="id")
             */
            protected $user;
            /**
             * @Id
             * @GeneratedValue(strategy="NONE")
             * @Column(name="preference_id",type="smallint",nullable=false)
             */
            protected $preference_id;
        {code}

        By default doctrine creates a table with 2 single keys (the preference key is the primary key) which is of course incorrect.
        Had to add @Column(name="user_id",type="integer") to the user column to fix the index problem but that introduced another problem. The entity no longer accepter \Entity\User as a value for user and takes only a smallint as defined.
        Hide
        Benjamin Eberlei added a comment -

        Can you drop the table and show the DDL generated by both "orm:schema-tool:create --dump-sql" and "orm:schema-tool:update --dump-sql" ?

        I checked with our testsuite and all the entities generate the correct primary keys.

        Show
        Benjamin Eberlei added a comment - Can you drop the table and show the DDL generated by both "orm:schema-tool:create --dump-sql" and "orm:schema-tool:update --dump-sql" ? I checked with our testsuite and all the entities generate the correct primary keys.
        Benjamin Eberlei made changes -
        Workflow jira [ 12898 ] jira-feedback [ 13956 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 13956 ] jira-feedback2 [ 15820 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 15820 ] jira-feedback3 [ 18076 ]
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Awaiting Feedback [ 10000 ]
        made changes -
        Status Awaiting Feedback [ 10000 ] In Progress [ 3 ]
        Hide
        Marco Pivetta added a comment -

        Is this still valid with newer versions of the ORM?

        Show
        Marco Pivetta added a comment - Is this still valid with newer versions of the ORM?
        Marco Pivetta made changes -
        Status In Progress [ 3 ] Awaiting Feedback [ 10000 ]
        Benjamin Eberlei made changes -
        Status Awaiting Feedback [ 10000 ] Open [ 1 ]
        Hide
        Benjamin Eberlei added a comment -

        No feedback given

        Show
        Benjamin Eberlei added a comment - No feedback given
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Resolution Incomplete [ 4 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DDC-1318, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Peter Jasiulewicz
          • Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: