setting up a one to many relationship for a self referencing table

user_7764329 0
Viewed 9626

I have a Project entity with a non-autogenerated id field and a successor field. This successor is the project that follows next. But maybe there is no following project so this might be null.

@Entity()
export class Project extends BaseEntity {
  @PrimaryColumn({ unique: true })
  public id: string;

  @OneToMany(() => Project, project => project.id, { nullable: true })
  public successorId?: string;
}

When creating a new project via

public createProject(id: string, successorId?: string): Promise<Project> {
  const project: Project = new Project();
  project.id = id;
  project.successorId = successorId;
  return project.save();
}

there are multiple cases I have to take care for.

  • Passing in an id that already exists:

    This will not throw an error. It just overrides the existing entity.

  • Passing in undefined for the successorId:

    The code works fine then but it does not create a successorId column with null then. The column simply does not exist in the database.

  • Passing in the same id for id and successorId (this should be possible):

    TypeORM throws the error

    TypeError: Cannot read property 'joinColumns' of undefined

  • Passing in a successorId of another existing project:

    I'm getting the same error as above

  • Passing in a successorId of a project that doesn't exist:

    I'm getting the same error as above

So how can I fix that? I think my entity design seems to be wrong. Basically it should be

  • One project might have one successor
  • A project can be the successor of many projects

Would be awesome if someone could help!

Update

I also tried this

@OneToMany(() => Project, project => project.successorId, { nullable: true })
@Column()
public successorId?: string;

but whenever I want to call the createProject method I'm getting this error

QueryFailedError: null value in column "successorId" violates not-null constraint

and this

@OneToMany(() => Project, project => project.successorId, { nullable: true })
public successorId?: string;

but then I'm getting this error

TypeError: relatedEntities.forEach is not a function

typeorm nestjs postgresql orm sql
2 Answers

Please try this solution

@Entity()
export class Project extends BaseEntity {
  @PrimaryColumn({ unique: true })
  public id: string;

  @Column({ nullable: true })
  public successorId?: string;

  @ManyToOne(() => Project, project => project.id)
  @JoinColumn({ name: "successorId" })
  public successor?: Project;
}

public successor?: Project; - property is used for building relation between entities (same entity in this case). Related entity must be specified as a property type, because TypeORM uses this type to determine target entity and build relation metadata. You can read more about TypeORM relations here

public successorId?: string; - property is just an "extracted" join column. When you use ManyToOne relation, TypeORM automatically creates a column in the database named propertyName + referencedColumnName (successorId in this case). But you cannot use this column in your code, because it is defined only in table and not in your class. And if you need this column defined in class (for further saving or displaying) you can create a property and mark it with a @Column decorator. Property name must be the same as the join column name in the table. Described in more detail here

Creating an entity with the same id just overrides the existing one

this is an expected behaviour. When you trying to save entity with an existing Id, TypeORM recognizes this as an update, not a create

user_90189460

You can try this

export class RolesPermission {
  @PrimaryGeneratedColumn('uuid')
  @PrimaryColumn({ type: 'varchar', length: 36, default: 'UUID()' })
  entityId?: string;

  @Column({ unique: true })
  name: string;

  @OneToMany(() => RolesPermission, (rolePermission) => rolePermission.parent)
  rolePermissions?: RolesPermission[];

  @ManyToOne(() => RolesPermission, (rolePermission) => rolePermission.rolePermissions, { nullable: true, createForeignKeyConstraints: false })
  parent?: RolesPermission;

  @Column({ type: 'datetime', default: () => 'CURRENT_TIMESTAMP' })
  createdAt?: Date;

  @Column({ type: 'datetime', default: () => 'CURRENT_TIMESTAMP', onUpdate: 'CURRENT_TIMESTAMP' })
  updatedAt?: Date;}
user_81370960
Related