doctrine/archived-orm
1
0
Fork 0
mirror of https://github.com/doctrine/orm.git synced 2026-03-23 22:42:18 +01:00
Code Issues 5.3k Packages Projects Releases 100 Wiki Activity

DDC-732: Class Table Inheritance - wrong table order on insert with more than one level of inheritance #904

New Issue
Closed
opened 2026-01-22 12:54:48 +01:00 by admin · 14 comments
admin commented 2026-01-22 12:54:48 +01:00
Owner
Copy Link

Originally created by @doctrinebot on GitHub (Aug 6, 2010).

Originally assigned to: @beberlei on GitHub.

Jira issue originally created by user vigor_bg:

This is the error that i get when i try to create new entity Employee

$newEmployee = new Users*Entity_Model*Employee;
//here i set the data
.....

$em->persist($newEmployee);
$em->flush();

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (crm/Employees, CONSTRAINT employee_person FOREIGN KEY (contactID) REFERENCES Persons (contactID) ON DELETE NO ACTION ON UPDATE NO ACTION)

/****
 *
 * @Entity
 * @InheritanceType("JOINED")
 * @Table(name="Contacts")
 * @DiscriminatorColumn(name="contactType", type="string")
 * @DiscriminatorMap({"PERSON" = "Users*Model_Entity_Person", "COMPANY" = "Users_Model_Entity_Company", "EMPLOYEE" = "Users_Model_Entity_Employee", "FIRM" = "Users_Model_Entity*Firm"})
 */
class Users*Model_Entity*Contacts 
{


    /****
     *  @Id  @Column(name="contactID", type="integer")
     *  @GeneratedValue(strategy="AUTO")
     *
     */
    protected $contactID;

    // ...

}

/****
 *
 * @Table(name="Persons")
 * @entity(repositoryClass="Users*Model*PersonRepo")


 */
class Users*Model_Entity_Person extends Users_Model_Entity*Contacts
{
 // ...
}




/****
 *
 * @Table(name="Employees")
 * @entity
 */
class Users*Model_Entity_Employee extends Users_Model_Entity*Person
{
    // .....
}
Originally created by @doctrinebot on GitHub (Aug 6, 2010). Originally assigned to: @beberlei on GitHub. Jira issue originally created by user vigor_bg: This is the error that i get when i try to create new entity Employee ``` $newEmployee = new Users*Entity_Model*Employee; //here i set the data ..... $em->persist($newEmployee); $em->flush(); ``` SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`crm/Employees`, CONSTRAINT `employee_person` FOREIGN KEY (`contactID`) REFERENCES `Persons` (`contactID`) ON DELETE NO ACTION ON UPDATE NO ACTION) ``` /**** * * @Entity * @InheritanceType("JOINED") * @Table(name="Contacts") * @DiscriminatorColumn(name="contactType", type="string") * @DiscriminatorMap({"PERSON" = "Users*Model_Entity_Person", "COMPANY" = "Users_Model_Entity_Company", "EMPLOYEE" = "Users_Model_Entity_Employee", "FIRM" = "Users_Model_Entity*Firm"}) */ class Users*Model_Entity*Contacts { /**** * @Id @Column(name="contactID", type="integer") * @GeneratedValue(strategy="AUTO") * */ protected $contactID; // ... } /**** * * @Table(name="Persons") * @entity(repositoryClass="Users*Model*PersonRepo") */ class Users*Model_Entity_Person extends Users_Model_Entity*Contacts { // ... } /**** * * @Table(name="Employees") * @entity */ class Users*Model_Entity_Employee extends Users_Model_Entity*Person { // ..... } ```
admin added the Bug label 2026-01-22 12:54:48 +01:00
admin closed this issue 2026-01-22 12:54:49 +01:00
admin commented 2026-01-22 12:54:50 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 6, 2010):

Comment created by vigor_bg:

I managed to fix it but i am not sure that this is the wright solution. The problem is in the
Doctrine\ORM\Persisters\JoinedSubclassPersister in the function executeInserts()

if ( ! $this->_queuedInserts) {
            return;
        }

        if ($this->_class->isVersioned) {
            $versionedClass = $this->_getVersionedClassMetadata();
        }

        $postInsertIds = array();
        $idGen = $this->_class->idGenerator;
        $isPostInsertId = $idGen->isPostInsertGenerator();

        // Prepare statement for the root table
        $rootClass = $this->*class->name == $this->*class->rootEntityName ?
                $this->*class : $this->_em->getClassMetadata($this->*class->rootEntityName);
        $rootPersister = $this->_em->getUnitOfWork()->getEntityPersister($rootClass->name);
        $rootTableName = $rootClass->table['name'];
        $rootTableStmt = $this->*conn->prepare($rootPersister->*getInsertSQL());

        // Prepare statements for sub tables.
        $subTableStmts = array();
        if ($rootClass !== $this->_class) {
            $subTableStmts[$this->*class->table['name']] = $this->_conn->prepare($this->*getInsertSQL());
        }
        foreach ($this->_class->parentClasses as $parentClassName) {
            $parentClass = $this->_em->getClassMetadata($parentClassName);
            $parentTableName = $parentClass->table['name'];
            if ($parentClass !== $rootClass) {
                $parentPersister = $this->_em->getUnitOfWork()->getEntityPersister($parentClassName);
                $subTableStmts[$parentTableName] = $this->*conn->prepare($parentPersister->*getInsertSQL());
            }
        }


        // Execute all inserts. For each entity:
        // 1) Insert on root table
        // 2) Insert on sub tables
        foreach ($this->_queuedInserts as $entity) {
            $insertData = $this->_prepareInsertData($entity);

            // Execute insert on root table
            $paramIndex = 1;
            foreach ($insertData[$rootTableName] as $columnName => $value) {
                $rootTableStmt->bindValue($paramIndex<ins></ins>, $value, $this->_columnTypes[$columnName]);
            }
            $rootTableStmt->execute();

            if ($isPostInsertId) {
                $id = $idGen->generate($this->_em, $entity);
                $postInsertIds[$id] = $entity;
            } else {
                $id = $this->_em->getUnitOfWork()->getEntityIdentifier($entity);
            }

            /****
             * Here is the fix that i made
             */
           $subTableStmts = array_reverse($subTableStmts);
            /****
             * end 
             */

            // Execute inserts on subtables.
            // The order doesn't matter because all child tables link to the root table via FK.
            foreach ($subTableStmts as $tableName => $stmt) {
                $data = isset($insertData[$tableName]) ? $insertData[$tableName] : array();
                $paramIndex = 1;
                foreach ((array) $id as $idVal) {
                    $stmt->bindValue($paramIndex<ins></ins>, $idVal);
                }
                foreach ($data as $columnName => $value) {
                    $stmt->bindValue($paramIndex<ins></ins>, $value, $this->_columnTypes[$columnName]);
                }
                $stmt->execute();
            }
        }

        $rootTableStmt->closeCursor();
        foreach ($subTableStmts as $stmt) {
            $stmt->closeCursor();
        }

        if (isset($versionedClass)) {
            $this->_assignDefaultVersionValue($versionedClass, $entity, $id);
        }

        $this->_queuedInserts = array();

        return $postInsertIds;
    }

the order of the queue for insert was not correct.

@doctrinebot commented on GitHub (Aug 6, 2010): Comment created by vigor_bg: I managed to fix it but i am not sure that this is the wright solution. The problem is in the Doctrine\ORM\Persisters\JoinedSubclassPersister in the function executeInserts() ``` if ( ! $this->_queuedInserts) { return; } if ($this->_class->isVersioned) { $versionedClass = $this->_getVersionedClassMetadata(); } $postInsertIds = array(); $idGen = $this->_class->idGenerator; $isPostInsertId = $idGen->isPostInsertGenerator(); // Prepare statement for the root table $rootClass = $this->*class->name == $this->*class->rootEntityName ? $this->*class : $this->_em->getClassMetadata($this->*class->rootEntityName); $rootPersister = $this->_em->getUnitOfWork()->getEntityPersister($rootClass->name); $rootTableName = $rootClass->table['name']; $rootTableStmt = $this->*conn->prepare($rootPersister->*getInsertSQL()); // Prepare statements for sub tables. $subTableStmts = array(); if ($rootClass !== $this->_class) { $subTableStmts[$this->*class->table['name']] = $this->_conn->prepare($this->*getInsertSQL()); } foreach ($this->_class->parentClasses as $parentClassName) { $parentClass = $this->_em->getClassMetadata($parentClassName); $parentTableName = $parentClass->table['name']; if ($parentClass !== $rootClass) { $parentPersister = $this->_em->getUnitOfWork()->getEntityPersister($parentClassName); $subTableStmts[$parentTableName] = $this->*conn->prepare($parentPersister->*getInsertSQL()); } } // Execute all inserts. For each entity: // 1) Insert on root table // 2) Insert on sub tables foreach ($this->_queuedInserts as $entity) { $insertData = $this->_prepareInsertData($entity); // Execute insert on root table $paramIndex = 1; foreach ($insertData[$rootTableName] as $columnName => $value) { $rootTableStmt->bindValue($paramIndex<ins></ins>, $value, $this->_columnTypes[$columnName]); } $rootTableStmt->execute(); if ($isPostInsertId) { $id = $idGen->generate($this->_em, $entity); $postInsertIds[$id] = $entity; } else { $id = $this->_em->getUnitOfWork()->getEntityIdentifier($entity); } /**** * Here is the fix that i made */ $subTableStmts = array_reverse($subTableStmts); /**** * end */ // Execute inserts on subtables. // The order doesn't matter because all child tables link to the root table via FK. foreach ($subTableStmts as $tableName => $stmt) { $data = isset($insertData[$tableName]) ? $insertData[$tableName] : array(); $paramIndex = 1; foreach ((array) $id as $idVal) { $stmt->bindValue($paramIndex<ins></ins>, $idVal); } foreach ($data as $columnName => $value) { $stmt->bindValue($paramIndex<ins></ins>, $value, $this->_columnTypes[$columnName]); } $stmt->execute(); } } $rootTableStmt->closeCursor(); foreach ($subTableStmts as $stmt) { $stmt->closeCursor(); } if (isset($versionedClass)) { $this->_assignDefaultVersionValue($versionedClass, $entity, $id); } $this->_queuedInserts = array(); return $postInsertIds; } ``` the order of the queue for insert was not correct.
admin commented 2026-01-22 12:54:50 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 8, 2010):

Comment created by @beberlei:

thanks for the report, but posting large bunches of changed code isnt very helpful. You should post patches/diffs instead using "git diff" or "diff" against the original file.

I checked your patch and it cannot be right, you check the reverse inside the loop each time. Without a failing test-case i cant do anything here, i will look into it in more detail later.

@doctrinebot commented on GitHub (Aug 8, 2010): Comment created by @beberlei: thanks for the report, but posting large bunches of changed code isnt very helpful. You should post patches/diffs instead using "git diff" or "diff" against the original file. I checked your patch and it cannot be right, you check the reverse inside the loop each time. Without a failing test-case i cant do anything here, i will look into it in more detail later.
admin commented 2026-01-22 12:54:52 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 9, 2010):

Comment created by vigor_bg:

Hi there sorry for that you are wright the reverse should be before the foreach. I will prepare the test- case and post it a bit later.

@doctrinebot commented on GitHub (Aug 9, 2010): Comment created by vigor_bg: Hi there sorry for that you are wright the reverse should be before the foreach. I will prepare the test- case and post it a bit later.
admin commented 2026-01-22 12:54:52 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 9, 2010):

Comment created by vigor_bg:

This is the database and the test case for inserting data and we have Class Table Inheritance. the problematic ones are public function testInsertInheritedTwo(){ } and public function testInsertInheritedThree(){ }

@doctrinebot commented on GitHub (Aug 9, 2010): Comment created by vigor_bg: This is the database and the test case for inserting data and we have Class Table Inheritance. the problematic ones are `public function testInsertInheritedTwo(){ }` and `public function testInsertInheritedThree(){ }`
admin commented 2026-01-22 12:54:54 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 9, 2010):

Comment created by vigor_bg:

Also i just updated to BETA3 and found one more problem in /Doctrine/DBAL/Statement.php in the 'function execute' on line 126. $this->_params = array(); This bit of code is braking the insert statements. Because it clears the currently bound parameters. Hope that is all the info you need if there is more information that is required please let me know.

@doctrinebot commented on GitHub (Aug 9, 2010): Comment created by vigor_bg: Also i just updated to BETA3 and found one more problem in /Doctrine/DBAL/Statement.php in the 'function execute' on line 126. $this->_params = array(); This bit of code is braking the insert statements. Because it clears the currently bound parameters. Hope that is all the info you need if there is more information that is required please let me know.
admin commented 2026-01-22 12:54:54 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 29, 2010):

Comment created by @beberlei:

What database and driver are you using? All three tests pass for me

@doctrinebot commented on GitHub (Aug 29, 2010): Comment created by @beberlei: What database and driver are you using? All three tests pass for me
admin commented 2026-01-22 12:54:54 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 30, 2010):

Comment created by @beberlei:

can you please use the SQL generated from Doctrine Schema-Tool and try again if this issue occurs to you? You have generated your own SQL, this might be the issue.

@doctrinebot commented on GitHub (Aug 30, 2010): Comment created by @beberlei: can you please use the SQL generated from Doctrine Schema-Tool and try again if this issue occurs to you? You have generated your own SQL, this might be the issue.
admin commented 2026-01-22 12:54:56 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Aug 31, 2010):

Comment created by vigor_bg:

Hi there,

I will try with the sql generated by Doctrine. But the problem I got was when I have more than 2 levels of the inheritance hierarchy. When try to make an insert trough the lowest level (in my example the DDC732Driver. Actually it was not working with the DDC732Employee entity as well) the order of inserting was not correct. First it went trough the correct entity (in my example the DDC732Contacts), but then instead of going through DDC732Person it try to go trough the last level (DDC732Driver or DDC732Employee depending on the example). And then the MySql said that it can not go trough. Also maybe the reason is that I am using InnoDB, but then it shouldn't really matter because I thought that the hierarchy inheritance is supposed to work with it. Actually I am almost 100% sure the problem is in the InnoDB could you please try with it. If it is still working for you i will reexamine my application and find the mistake.

Thanks in advance for the help and sorry. Now on second thought I should have explained all that in the beginning.

@doctrinebot commented on GitHub (Aug 31, 2010): Comment created by vigor_bg: Hi there, I will try with the sql generated by Doctrine. But the problem I got was when I have more than 2 levels of the inheritance hierarchy. When try to make an insert trough the lowest level (in my example the DDC732Driver. Actually it was not working with the DDC732Employee entity as well) the order of inserting was not correct. First it went trough the correct entity (in my example the DDC732Contacts), but then instead of going through DDC732Person it try to go trough the last level (DDC732Driver or DDC732Employee depending on the example). And then the MySql said that it can not go trough. Also maybe the reason is that I am using InnoDB, but then it shouldn't really matter because I thought that the hierarchy inheritance is supposed to work with it. Actually I am almost 100% sure the problem is in the InnoDB could you please try with it. If it is still working for you i will reexamine my application and find the mistake. Thanks in advance for the help and sorry. Now on second thought I should have explained all that in the beginning.
admin commented 2026-01-22 12:54:56 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 15, 2010):

Comment created by @beberlei:

Doctrine2 only creates InnoDB tables. Are you using foreign keys in a different way?

@doctrinebot commented on GitHub (Sep 15, 2010): Comment created by @beberlei: Doctrine2 only creates InnoDB tables. Are you using foreign keys in a different way?
admin commented 2026-01-22 12:54:58 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Sep 21, 2010):

Comment created by @beberlei:

Closed because we can't reproduce this. Schema of the user differs from the ORM SchemaTool and causes this issue.

@doctrinebot commented on GitHub (Sep 21, 2010): Comment created by @beberlei: Closed because we can't reproduce this. Schema of the user differs from the ORM SchemaTool and causes this issue.
admin commented 2026-01-22 12:54:58 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Oct 11, 2010):

Comment created by vigor_bg:

Hi there i am reopening this ticket, because i found problem with the logic of creating inherited join tables from your create schema tool. I was expecting when we have more than one level of inherited tables the foreign key constrain to go to the parent and not to the root table. And because of that with the sql generated from your tool it was ok and it was not working with my sql. I would like to know are you going to fix that or it was intended to work that way from the beginning? Because logically for me and my colleagues if Employee extends Person then the foreign key constrain in Employee should go to Person and at the same time Person extends Contact then foreign key constrain in Person should go to Contact. And with your schema tool in that instance the foreign key constrain in Employee go to Contact which is wrong.

@doctrinebot commented on GitHub (Oct 11, 2010): Comment created by vigor_bg: Hi there i am reopening this ticket, because i found problem with the logic of creating inherited join tables from your create schema tool. I was expecting when we have more than one level of inherited tables the foreign key constrain to go to the parent and not to the root table. And because of that with the sql generated from your tool it was ok and it was not working with my sql. I would like to know are you going to fix that or it was intended to work that way from the beginning? Because logically for me and my colleagues if Employee extends Person then the foreign key constrain in Employee should go to Person and at the same time Person extends Contact then foreign key constrain in Person should go to Contact. And with your schema tool in that instance the foreign key constrain in Employee go to Contact which is wrong.
admin commented 2026-01-22 12:54:59 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Oct 30, 2010):

Comment created by @beberlei:

This is a documentation issue.

I have now documented the SQL schema requirements for inheritance scenarios.

Closing this issue.

@doctrinebot commented on GitHub (Oct 30, 2010): Comment created by @beberlei: This is a documentation issue. I have now documented the SQL schema requirements for inheritance scenarios. Closing this issue.
admin commented 2026-01-22 12:55:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Oct 30, 2010):

Issue was closed with resolution "Fixed"

@doctrinebot commented on GitHub (Oct 30, 2010): Issue was closed with resolution "Fixed"
admin commented 2026-01-22 12:55:00 +01:00
Author
Owner
Copy Link

@doctrinebot commented on GitHub (Dec 13, 2015):

Imported 2 attachments from Jira into https://gist.github.com/bf11a908ac1d309efd6c

@doctrinebot commented on GitHub (Dec 13, 2015): Imported 2 attachments from Jira into https://gist.github.com/bf11a908ac1d309efd6c - [10729_testDoc.sql](https://gist.github.com/bf11a908ac1d309efd6c#file-10729_testDoc-sql) - [10730_DDC732Test.php](https://gist.github.com/bf11a908ac1d309efd6c#file-10730_DDC732Test-php)
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
BC Break
Bug
Can't Fix
Deprecation
Documentation
DQL
Duplicate
Failing Test
Hacktoberfest
Improvement
Improvement
Incomplete
Invalid
Missing Tests
New Feature
PR Created
pull-request
Mirrored from GitHub Pull Request
 Question
Regression
Status: Waiting feedback
Waiting feedback
WIP
Won't Fix
No Label Bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
admin
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: doctrine/archived-orm#904
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?