How to specify foreign key column for Class Table Inheritance in Doctrine2?

Question

How can I specify the columns that are used for the foreign key relation with Class Table Inheritance in Doctrine 2? For example, take the following two classes:

/**
 * @Entity
 * @InhertanceType("JOINED")
 * @DiscriminatorColumn(name="type", type="string")
 * @DiscriminatorMap("person" = "Person", "employee" = "Employee")
 */
class Person
{
    /** @Id */
    public $id;

    /** @Column(type="string") */
    public $ssn;
}

/** @Entity */
class Employee
{
    /** @Column(type="decimal") */
    public $salary;
}

With this, Doctrine expects a table structure somewhat along this:

CREATE TABLE `person` (
    `id` INT(11) NOT NULL auto_increment,
    `ssn` VARCHAR(255) default NULL,
    PRIMARY_KEY(`id`)
)

CREATE TABLE `employee` (
    `person_id` INT(11) NOT NULL,
    `salary` DECIMAL(10,2) default NULL,
    PRIMARY_KEY(`person_id`)
)
ALTER TABLE `employee`
    ADD CONSTRAINT `person_fk` FOREIGN KEY (`person_id`)
        REFERENCES `person` (`id`) ON DELETE CASCADE

There is a foreign key employee.person_id that points to person.id. But how can I tell Doctrine which columns to use? I assume that the person.id reference comes from the @Id annotation on the Person class, but what if I want to create the FK to person.ssn instead (it's conceivable, since SSN are naturally unique).

And what if I have a legacy database where employee.person_id is called employee.p_id instead?

Answer 1

First of all, you are missing the "extends Person" in the Employee class definition.

From what I know, when you are doing this type of inheritance, doctrine uses the primary key in the main class to join all other tables, its is expected behavior.

When you do a query, doctrine will join all the child tables to the parent and then hydrate according to the discriminator map. With that in mind, it doesn't matter if your primary key is an automatically incremental id or a unique field. If you have ssn defined in the parent class, it will be available for searching in all your subclasses.

So a couple of tips here. You can if you want, remove the automatical id and use ssn as your primary key, then doctrine expects that all of your child tables to have that same field defined in them. For performance it may be wise to have an integer instead of a 255 string to do the joins anyway.

If you want to mantain the automated id, you may want to add a unique index to the parent class, that way if you access the classes by that field, you wont get performance slowdowns.

If you want to have the class name to be something and the table name to be something else, use this

/** @Id @Column(name="p_id") */
public $id;

But then remember that all the tables that are part of the inheritance should use that name.

Moreover, I normally use doctrine for mapping an existing database and extending it (migrate to doctrine), and if I'm adding a new feature and the model requires so, I create the mapping and tables myself keeping in mind how Doctrines inheritance works. But if you have existing tables that think can be modeled with Inheritance, expect some trouble, and maybe the needs to modify the existing tables.

Hope this helps.