Incorrect insert order when cascade inserting parent inherited relations

[konradkevin]

Issue Description

Expected Behavior

When I insert a child entity with nested relations inherited from a parent, I want the child entity to be inserted prior to the relations in order not to break not null constraints on the foreign keys

Actual Behavior

When I insert a child entity with nested relations inherited from a parent, the relations are inserted before the child entity with NULL value as a foreign key and then are updated with the correct foreign key once the entity has been inserted

Thus, we cannot use this foreign key as part of a composite primary key as it must stay nullable so that the transaction does not break

QueryFailedError: SQLITE_CONSTRAINT: NOT NULL constraint failed: user_photo.userId
    at Statement.handler (C:\Users\PKKD01221\Workspace\typeorm\src\driver\sqlite\SqliteQueryRunner.ts:113:26) {
  query: 'INSERT INTO "user_photo"("isProfilePhoto", "userId", "photoId") VALUES (1, NULL, 1)',
  parameters: [],
  driverError: [Error: SQLITE_CONSTRAINT: NOT NULL constraint failed: user_photo.userId] {
    errno: 19,
    code: 'SQLITE_CONSTRAINT'
  },
  errno: 19,
  code: 'SQLITE_CONSTRAINT'
}
QueryFailedError: SqliteError: NOT NULL constraint failed: user_photo.userId
    at BetterSqlite3QueryRunner.query (C:\Users\PKKD01221\Workspace\typeorm\src\driver\better-sqlite3\BetterSqlite3QueryRunner.ts:138:19)
    at InsertQueryBuilder.execute (C:\Users\PKKD01221\Workspace\typeorm\src\query-builder\InsertQueryBuilder.ts:161:33)
    at SubjectExecutor.executeInsertOperations (C:\Users\PKKD01221\Workspace\typeorm\src\persistence\SubjectExecutor.ts:428:42)
    at SubjectExecutor.execute (C:\Users\PKKD01221\Workspace\typeorm\src\persistence\SubjectExecutor.ts:137:9)
    at EntityPersistExecutor.execute (C:\Users\PKKD01221\Workspace\typeorm\src\persistence\EntityPersistExecutor.ts:194:21)
    at Context.<anonymous> (C:\Users\PKKD01221\Workspace\typeorm\test\github-issues\9999\issue-9999.ts:55:17) {
  query: 'INSERT INTO "user_photo"("isProfilePhoto", "userId", "photoId") VALUES (1, NULL, 1)',
  parameters: [],
  driverError: SqliteError: NOT NULL constraint failed: user_photo.userId
      at BetterSqlite3QueryRunner.query (C:\Users\PKKD01221\Workspace\typeorm\src\driver\better-sqlite3\BetterSqlite3QueryRunner.ts:110:38)
      at InsertQueryBuilder.execute (C:\Users\PKKD01221\Workspace\typeorm\src\query-builder\InsertQueryBuilder.ts:161:33)
      at SubjectExecutor.executeInsertOperations (C:\Users\PKKD01221\Workspace\typeorm\src\persistence\SubjectExecutor.ts:428:42)
      at SubjectExecutor.execute (C:\Users\PKKD01221\Workspace\typeorm\src\persistence\SubjectExecutor.ts:137:9)
      at EntityPersistExecutor.execute (C:\Users\PKKD01221\Workspace\typeorm\src\persistence\EntityPersistExecutor.ts:194:21)
      at Context.<anonymous> (C:\Users\PKKD01221\Workspace\typeorm\test\github-issues\9999\issue-9999.ts:55:17) {
    code: 'SQLITE_CONSTRAINT_NOTNULL'
  },
  code: 'SQLITE_CONSTRAINT_NOTNULL'
}

Steps to Reproduce

@Entity()
@TableInheritance({ column: { type: "varchar", name: "type" } })
export class User {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @OneToMany(() => UserPhoto, (userPhoto) => userPhoto.user, { cascade: true })
    userPhotos: UserPhoto[]
}

@Entity()
export class Photo {
    @PrimaryGeneratedColumn()
    id: number

    @Column()
    name: string

    @OneToMany(() => UserPhoto, (userPhoto) => userPhoto.photo, { cascade: true })
    userPhotos: UserPhoto[]
}

@Entity()
export class UserPhoto {
    @Column()
    isProfilePhoto: boolean

    @ManyToOne(() => User, (user) => user.userPhotos, { nullable: false })
    user: User

    @PrimaryColumn()
    userId: User['id']

    @ManyToOne(() => Photo, (photo) => photo.userPhotos, { nullable: false })
    photo: Photo

    @PrimaryColumn()
    photoId: Photo['id']
}

@ChildEntity()
export class Employee extends User {
    @Column()
    salary: number
}

const photos: DeepPartial<Photo>[] = [
    { name: 'Photo 1' },
    { name: 'Photo 2' },
]

await connection.getRepository(Photo).save(photos)

const employee: DeepPartial<Employee> = {
    name: 'test name',
    salary: 12345,
    userPhotos: [
        {
            photo: photos[0],
            isProfilePhoto: true
        },
        {
            photo: photos[1],
            isProfilePhoto: false
        }
    ]
}

const employeeRepository = connection.getRepository(Employee)
const createdEmployee = employeeRepository.create(employee)
await employeeRepository.save(createdEmployee)

Are you willing to resolve this issue by submitting a Pull Request?

• ✅ Yes, I have the time, and I know how to start.
• ✖️ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✖️ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

The problem is located in src/persistence/SubjectTopoligicalSorter.ts which does not check the entity parent type to sort the insert commands

         sortedNonNullableEntityTargets.forEach((sortedEntityTarget) => {
-            const entityTargetSubjects = this.subjects.filter((subject) => subject.metadata.targetName === sortedEntityTarget);
+            const entityTargetSubjects = this.subjects.filter((subject) => subject.metadata.targetName === sortedEntityTarget || subject.metadata.parentEntityMetadata?.targetName);
             sortedSubjects.push(...entityTargetSubjects);
             this.removeAlreadySorted(entityTargetSubjects);
         });

Related PR: #9242