DDC-1318: Unexpectet behavior while using ManyToOne as part ofr composite key

[doctrinebot]

Jira issue originally created by user pejot:

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.

[doctrinebot]

Comment created by @beberlei:

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

[doctrinebot]

Comment created by pejot:

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;
<ins>------------------</ins>------------<ins>------------------------------</ins>--------------<ins>---------------</ins>-----------<ins>-------------</ins>----------<ins>--------</ins>------<ins>------------</ins>---------<ins>
| Table            | Non*unique | Key_name                     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index*type | Comment |
</ins>------------------<ins>------------</ins>------------------------------<ins>--------------</ins>---------------<ins>-----------</ins>-------------<ins>----------</ins>--------<ins>------</ins>------------<ins>---------</ins>
| 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      |         |
<ins>------------------</ins>------------<ins>------------------------------</ins>--------------<ins>---------------</ins>-----------<ins>-------------</ins>----------<ins>--------</ins>------<ins>------------</ins>---------<ins>
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;
</ins>------------------<ins>------------</ins>----------<ins>--------------</ins>---------------<ins>-----------</ins>-------------<ins>----------</ins>--------<ins>------</ins>------------<ins>---------</ins>
| Table            | Non*unique | Key_name | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index*type | Comment |
<ins>------------------</ins>------------<ins>----------</ins>--------------<ins>---------------</ins>-----------<ins>-------------</ins>----------<ins>--------</ins>------<ins>------------</ins>---------<ins>
| user*preferences |          0 | PRIMARY  |            1 | user*id       | A         |           0 |     NULL | NULL   |      | BTREE      |         |
| user*preferences |          0 | PRIMARY  |            2 | preference*id | A         |           0 |     NULL | NULL   |      | BTREE      |         |
</ins>------------------<ins>------------</ins>----------<ins>--------------</ins>---------------<ins>-----------</ins>-------------<ins>----------</ins>--------<ins>------</ins>------------<ins>---------</ins>
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;
<ins>------------------</ins>------------<ins>------------------------------</ins>--------------<ins>---------------</ins>-----------<ins>-------------</ins>----------<ins>--------</ins>------<ins>------------</ins>---------<ins>
| Table            | Non*unique | Key_name                     | Seq_in_index | Column_name   | Collation | Cardinality | Sub_part | Packed | Null | Index*type | Comment |
</ins>------------------<ins>------------</ins>------------------------------<ins>--------------</ins>---------------<ins>-----------</ins>-------------<ins>----------</ins>--------<ins>------</ins>------------<ins>---------</ins>
| 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      |         |
<ins>------------------</ins>------------<ins>------------------------------</ins>--------------<ins>---------------</ins>-----------<ins>-------------</ins>----------<ins>--------</ins>------<ins>------------</ins>---------+
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

[doctrinebot]

Comment created by @beberlei:

Formatted code.

[doctrinebot]

Comment created by @beberlei:

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.

[doctrinebot]

Comment created by @Ocramius:

Is this still valid with newer versions of the ORM?

[doctrinebot]

Comment created by @beberlei:

No feedback given

[doctrinebot]

Issue was closed with resolution "Incomplete"