Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to reference a column other than 'id' for a JoinColumn?

I have an Item entity that has a ManyToOne relationship to a Category entity. I want them to be joined by a field other than Category's id (in this case, a field called id2). My schema is listed below.

class Item {
    /**
     * @ORM\Id
     * @ORM\Column(name = "id", type = "integer")
     * @ORM\GeneratedValue(strategy = "AUTO")
     */
    protected $id;
    /**
     * @ORM\ManyToOne(targetEntity = "Category")
     * @ORM\JoinColumn(name = "category_id", referencedColumnName = "id2")
     */
    protected $category;
}

class Category {
    /**
     * @ORM\Id
     * @ORM\Column(name = "id", type = "integer")
     * @ORM\GeneratedValue(strategy = "AUTO")
     */
    protected $id;
    /**
     * @ORM\Column(name = "id2", type = "string", length = "255", unique = "true")
     */
    protected $id2;

When I try saving an Item I get this error:

Notice: Undefined index: id2 in vendor/doctrine/lib/Doctrine/ORM/Persisters/BasicEntityPersister.php line 511

Sure enough, if I change id2 to id in the JoinColumn annotation, everything works fine, but I need the entities to be connected through id2. Is this possible?

Edit
What I want to achieve is impossible according to the official Doctrine 2 docs.

It is not possible to use join columns pointing to non-primary keys. Doctrine will think these are the primary keys and create lazy-loading proxies with the data, which can lead to unexpected results. Doctrine can for performance reasons not validate the correctness of this settings at runtime but only through the Validate Schema command.

source: https://www.doctrine-project.org/projects/doctrine-orm/en/2.6/reference/limitations-and-known-issues.html#join-columns-with-non-primary-keys

like image 651
Steven Mercatante Avatar asked Jan 19 '12 01:01

Steven Mercatante


2 Answers

I think Doctrine wants these to be primary keys, from the docs:

name: Column name that holds the foreign key identifier for this relation.

Another thing that jumps out at me from your code sample is category.id2 being type string, I would at least expect it to be an integer, but it may also need to be for @JoinColumn to work properly.

You may be able to get away with just @Index on category.id2 and leave it as a string though; worth a shot anyway.

like image 124
quickshiftin Avatar answered Oct 18 '22 02:10

quickshiftin


Just to report. I was able to join non-PKs in Many2One (undirectional) relation, BUT my object can't be loaded the normal way. It must be loaded with DQL like:

SELECT d,u FROM DEntity d
    JOIN d.userAccount u

this way I stopped getting error: Missing value for primary key id on ....

like image 39
Anton Valqk Avatar answered Oct 18 '22 02:10

Anton Valqk