[DDC-117] Allow @Id on @ManyToOne fields Created: 05/Nov/09  Updated: 09/Jan/11  Resolved: 02/Jan/11

Status: Resolved
Project: Doctrine 2 - ORM
Component/s: None
Affects Version/s: 2.0-ALPHA3
Fix Version/s: 2.1
Security Level: All

Type: New Feature Priority: Major
Reporter: Nico Kaiser Assignee: Benjamin Eberlei
Resolution: Fixed Votes: 8
Labels: None

Attachments: File ddc117-20101228.diff     Text File ddc117test_cascade_persist.patch     Text File ddc117_20100815.patch    
Issue Links:
Duplicate
is duplicated by DDC-795 Wrong SQL statement when using loadOn... Resolved
Sub-Tasks:
Key
Summary
Type
Status
Assignee
DDC-658 Reverse engineering with Oracle (DBDr... Sub-task Open Benjamin Eberlei  
DDC-795 Wrong SQL statement when using loadOn... Sub-task Resolved Benjamin Eberlei  
DDC-881 DDC-117: Linked Objects with composit... Sub-task Resolved Roman S. Borschel  
DDC-922 Persisting an entity with an 1:n coll... Sub-task Resolved Benjamin Eberlei  

 Description   

Sometimes, a @ManyToOne field has to be the Primary Key, or part of the Primary Key. Adding @Id to the @ManyToOne does not help, an additional property with duplicates the referenced id is needed:

(userId and User field in the Phonenumber class - to be able to set @Id on userId):
http://pastebin.com/d51e021e2

Allowing @Id on @ManyToOne fields (which would make the JoinColumn a PK) would help here. Any maybe this would also fix DDC-116



 Comments   
Comment by Nico Kaiser [ 07/Dec/09 ]

Thanks for your reply on doctrine-user!

My previous workaround was something like this:
http://pastie.org/private/uoawwvf75onnvph8bj1fwg

Instead of just having a $User property in the Phonenumber entity (which is mapped by the userId field in the phonenumber table), I have an additional $userId property, which is also mapped to the userId DB field. This way I could make $userId @Id (and thus add it to the PKs), but I had to manually update it when I set the $User (see the TODO annotations in the Pastie code).

I see "EntityManager#find(...)" would have to be able to also get objects as identifiers, e.g. (int, User), which may have major implications...

However I think to support constructions like this is very important as it's a very common pattern, especially for OneToOne associations with no additional identifier field (PK = FK)...

Comment by Roman S. Borschel [ 17/Dec/09 ]

We might need to introduce the concept of an IdClass for this in order to be implemented decently.

Comment by Marcus Stöhr [ 15/Jun/10 ]

I just ran into this exact issue. Is there any decent way to work around this issue as the pastie.org-Link is already gone?

Comment by Nico Kaiser [ 15/Jun/10 ]

In an Entity (e.g. "Phonenumber") with PrimaryKey userId you can do something like this:

/**
 * @ManyToOne(targetEntity="Entities\User", inversedBy="Phonenumbers")
 * @JoinColumn(name="userId", referencedColumnName="id")
 */
protected $User;

/**
 * @Id
 * @Column(name="userId", type="integer")
 */
protected $userId;

public function setUser(\Entities\User $user)
{
    $this->User = $user;
    $this->userId = $user->getId();
}
Comment by Benjamin Eberlei [ 07/Aug/10 ]

I took the time today and tried what is possible to hack in this regard and came up with a pretty trivial solution for this. This is a very early draft of this functionality as it might be included in 2.1

http://github.com/doctrine/doctrine2/commits/DDC-117

What works?

  • Association Only Composite Primary Keys (Reference with SourceArticle to TargetArticle)
  • Mixed Composite Primary Keys (ArticleTranslation with Article + Language)
  • Single Association Primary Keys (Article and ArticleDetails)
Comment by Marcus Stöhr [ 07/Aug/10 ]

Wow, great work, Benjamin. Will try it out ASAP and when it works, I'll owe you a beer.

Comment by s9e [ 08/Aug/10 ]

I'm having troubles with user-defined column names used in SAPK. The schema tool complains that the column name doesn't exist. I've reduced it to a small testcase based on DDC117Test.

Comment by s9e [ 08/Aug/10 ]

SAPK with user-defined column names

Comment by Benjamin Eberlei [ 15/Aug/10 ]

I fixed another bunch of issues with hydration and updating of assoc-id entities.

@s9e i will now tackle your issue.

Comment by Benjamin Eberlei [ 15/Aug/10 ]

@s9e you forgot to define the @JoinColumn annotation correctly. that is necessary when you rename the ID column on the other side. See my current commit, it works for me!

The commit is: http://github.com/doctrine/doctrine2/commit/772e5924898326de2c769c4cb0c6874fde4edc45

Comment by Benjamin Eberlei [ 15/Aug/10 ]

Add current version of the patch diffed against the master from today, for easier testing and review.

Comment by s9e [ 15/Aug/10 ]

@Benjamin Eberlei - Actually it's the other way around. I have defined @JoinColumn on both sides of the relationship, and SchemaTool doesn't like that. After removing @JoinColumn from the inverse side, SchemaTool processes the entities as expected, so I'm not sure whether it should be considered a bug or a feature. If @JoinColumn should only be defined on the owning side, please add a note to the manual.

Anyway, the schema now works but I'm still having troubles persisting through cascade. Test attached.

Comment by Benjamin Eberlei [ 16/Aug/10 ]

the @joinColumn is explained in the association mapping chapter. There are examples for each cases, showing where to put the annotation and where not.

The Persist Cascade i pick up next then.

Comment by Marc Hodgins [ 17/Aug/10 ]

Hi Benjamin, great work on this. Testing it out now. One problem (do you want a ticket opened?). Using the latest DDC-117 branch, schema tool crates both a PRIMARY KEY and a UNIQUE KEY (at least in MySQL) on the same columns. The UNIQUE KEY is really not necessary, right?

/** @Entity */
class Foo {
    /**
     * @Id @Column(type="integer")
     * @GeneratedValue
     */
    protected $id;
}

/** @Entity */
class Bar {
    /**
     * @Id
     * @OneToOne(targetEntity="Foo")
     * @JoinColumn(name="foo_id", referencedColumnName="id")
     */
    protected $fooId;
}

Produces:

CREATE TABLE Bar (foo_id INT NOT NULL, UNIQUE INDEX Bar_foo_id_uniq (foo_id), PRIMARY KEY(foo_id)) ENGINE = InnoDB;
CREATE TABLE Foo (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDB;

Expected:

CREATE TABLE Bar (foo_id INT NOT NULL, PRIMARY KEY(foo_id)) ENGINE = InnoDB;
CREATE TABLE Foo (id INT AUTO_INCREMENT NOT NULL, PRIMARY KEY(id)) ENGINE = InnoDB;
Comment by Benjamin Eberlei [ 17/Aug/10 ]

No, all the issues on this ticket. I branch them into subtickets if necessary.

Comment by Marc Hodgins [ 14/Nov/10 ]

Hi Benjamin - would you mind doing a merge from current master to DDC-117 branch? Been using it for devel and it is working quite well but commit 140ddf5098a7ffdf6bc3 on Sept 27 causes a merge conflict. Looks like it is easy to resolve but it would be great if it could be merged into your DDC-117 so extra steps aren't required to clone/checkout DDC-117.

Comment by Benjamin Eberlei [ 28/Dec/10 ]

I updated the branch to integrate with the current master and attached a new patch to the ticket.

Comment by Benjamin Eberlei [ 28/Dec/10 ]

@S9e: Yes this is obvious, it works only with Sequence as ID Generation strategy (for example with PostgreSQL)

The problem is, during persist MySQL and SQLite don't know the value of the primary key yet. However for the @id + @ManyToOne patch to work it is necessary that all the dependencies primary keys are already known. That is why two step flush procedures are sometimes necessary.

1. persist non fk+pk entities and flush them
2. persist fk+pk entities then flush them

Comment by Benjamin Eberlei [ 01/Jan/11 ]

Patch is now finished and will be merged into master tomorrow.

Comment by Benjamin Eberlei [ 02/Jan/11 ]

Merged into master and scheduled for 2.1.

Please test this patch extensively, there are tons of examples in tests/Doctrine/Tests/Models/DDC117 and tests/Doctrine/Tests/ORM/Functional/Ticket/DDC117Test.php

Comment by Henrik Bjornskov [ 09/Jan/11 ]

@Benjamin

I have created a OneToOne relation on a User -> Profile where the profile is specified as the inverse side so i can have user_id be the primary key. And it all works as expected except that when i create a new User (The Profile object is getting created in the user constructor) and persist it (the relation have cascade-all specified) i end up with the error "The given entity has no identity.". But if i persist the user without creating the profile inside it and fliush and then create a Profile object and persist and flush that it all work.

It seems like it dosent know that the User should be saved first so that it have an id and then save the profile. Dont know if this is a bug or it is expected behavior.

Comment by Benjamin Eberlei [ 09/Jan/11 ]

expected behavior, you are using the id generator "assigned" which means on persist the id has to be assigned. In your case it isnt, because the related object has not been assigned an id itself, thus failing.

Generated at Mon Nov 24 22:43:51 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.