Unexpectet behavior while using ManyToOne as part ofr composite key

Details

Type: Bug
Status: Resolved
Priority: 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

Descending order - Click to sort in ascending order

Hide

Permalink

Benjamin Eberlei added a comment - 01/May/13 6:40 PM

No feedback given

Show

Benjamin Eberlei added a comment - 01/May/13 6:40 PM No feedback given

Hide

Permalink

Marco Pivetta added a comment - 23/Jan/13 10:55 PM

Is this still valid with newer versions of the ORM?

Show

Marco Pivetta added a comment - 23/Jan/13 10:55 PM Is this still valid with newer versions of the ORM?

Hide

Permalink

Benjamin Eberlei added a comment - 06/Aug/11 4:26 PM

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 - 06/Aug/11 4:26 PM 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.

Hide

Permalink

Benjamin Eberlei added a comment - 06/Aug/11 4:24 PM

Formatted code.

Show

Benjamin Eberlei added a comment - 06/Aug/11 4:24 PM Formatted code.

Hide

Permalink

Peter Jasiulewicz added a comment - 04/Aug/11 10:43 AM - 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 - 04/Aug/11 10:43 AM - 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

Permalink

Benjamin Eberlei added a comment - 04/Aug/11 9:34 AM

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 - 04/Aug/11 9:34 AM This is probably because of the @GeneratedStrategy annotation, can you try to just remove it? For me there is always only one key generated.

People

Assignee:

Benjamin Eberlei

Reporter:

Peter Jasiulewicz

Vote (0)

Watch (2)

Dates

Created:

04/Aug/11 7:42 AM

Updated:

01/May/13 6:40 PM

Resolved:

01/May/13 6:40 PM