Unable to delete subordinate relation entity when saving parent entity

[superical]

Issue type:

[ ] question
[x] bug report
[ ] feature request
[ ] documentation issue

Database system/driver:

[ ] cordova
[ ] mongodb
[ ] mssql
[x] mysql / mariadb
[ ] oracle
[ ] postgres
[ ] cockroachdb
[ ] sqlite
[ ] sqljs
[ ] react-native
[ ] expo

TypeORM version:

[x ] latest
[ ] @next
[ ] 0.x.x (or put your version here)

Steps to reproduce or a small repository showing the problem:

My intention was to remove the subordinate entities in a relation from the parent entity.

For example, I've a Photo entity which has a one-to-many relation setup with another entity MetaData. So, when I retreive a Photo entity with MetaData relation, the Photo entity should have a shape like this:

    const photoEntity = this.photoRepository.findOne({where: {id: 5}, relations: ['metaData'])
    photoEntity: {
       file: 'someimage.jpg',
       metaDatas: [
          {
             type: 'date',
             data: '12-12-2000'
          },
          {
             type: 'location',
             data: '123.00 123.00'
          }
       ]
    }

Basically, the metaDatas property in Photo entity will be an array. My intention is to remove all MetaData from the Photo entity.

What I did was simply by setting the array of photoEntity.metaDatas to an empty array and then save photoEntity like so:

    const photoEntity = this.photoRepository.findOne({where: {id: 5}, relations: ['metaData'])
    // One photo can have many metadata rows of information in photoEntity.metaDatas
    // Photo entity has a one-to-many relationship setup with MetaData entity
    photoEntity.metaDatas = []   //  Remove all metaDatas in photoEntity
    
    this.photoRepository.save(photoEntity)   // Throws error that ER_BAD_NULL_ERROR: Column 'photoId' cannot be null

However, this throws an error that Column 'photoId' cannot be null. This is because instead of running a DELETE query, the SQL TypeORM did an UPDATE this:

sql: 'UPDATE `metaData` SET `photoId` = NULL, `updatedAt` = CURRENT_TIMESTAMP WHERE `metaDataId` = `123` }

Shouldn't a DELETE query be run instead of an update one?
In this case, how can I remove the MetaData entities from the Photo entity?

[Kononnable]

Can you provide your entity definitions? Without them we have to guess how exactly they're defined and which statement should be used.

[amiiit]

I'm facing a similar issue when updating the instances on the Many side of a One to Many relation. When setting one.many = [] seems like typeorm is trying to set oneId to null instead of just deleting this stuff. I'm using postgres.

The relevant part of my code is:

@Entity()
export class Event extends BaseEntity {
  @PrimaryGeneratedColumn("uuid")
  id: number

   @OneToMany(type => EventOccurrence, occurrence => occurrence.event, {
    cascade: true
  })
  occurrences: Promise<EventOccurrence[]>

  updateOccurrences() {
    const occurences = []
    const occ = new EventOccurrence()
    occ.during = `[${this.time.start},${this.time.end}]`
    occurences.push(occ)

    occurences.forEach(occ => {
      occ.start = this.time.start
      occ.end = this.time.end
      occ.event = Promise.resolve(this)
    })

    this.occurrences = Promise.resolve(occurences)
    return
  }
}

Then running event.updateOccurrences() and then saving, instead of removing the existing event.occurrences it seems like it tries to break the relation by setting eventId to null, which is not what I wanted.

Is there a way to make typeorm delete the old manys and replace them with the new one?

Thanks

[Kononnable]

I think you're looking for this: #1460

[brau0060]

@amiiit I'm having the same issue, what did you do to fix this. I have tried onUpdate: 'CASCADE' but it still adds null to the old many's and doesn't delete them?

[Nemolo]

i'm also having the same issue, and adding onUpdate: 'CASCADE' does not work

[ericmartinezr]

I have the same issue. I did "solve" it by deleting the property instead of assigning an empty array. The problem persists if you remove one of many elements and the array is not empty.

[greenreign]

Any advice on this issue @pleerock? I know it's always been this way (ie typeorm will not handle it) but maybe just updating this issue with that direction would help. Then we know it's not something we've misconfigured. We have to manage it ourselves.

[shindiogo]

Hi @greenreign
I have the same problem. Did you find the solution?

[yleflour]

For anyone still looking for a fix, here is a patch to correct this. Use it with patch-package.

https://gist.github.com/yleflour/0bda286aef0b80c16000ae7c93107178

I don't think I will be opening an official PR with this change as it would be an undocumented cascading effect. A proper solution should require its own configuration parameter in the OneToMany relationship decorator

[shindiogo]

For anyone still looking for a fix, here is a patch to correct this. Use it with patch-package.

https://gist.github.com/yleflour/0bda286aef0b80c16000ae7c93107178

I don't think I will be opening an official PR with this change as it would be an undocumented cascading effect. A proper solution should require its own configuration parameter in the OneToMany relationship decorator

Hi @yleflour ,

Thanks for you sugestion. I tried to implement it, but i get the same error.
I will share with you:

My package edited

Team entity

Team Element

Can you help me?

[yleflour]

@shindiogo My check uses the !isNullable on the FK to know if it has to be deleted which might not be for the best.
Can you check the relation.inverseRelation.isNullable value at runtime ?

I believe that adding the option nullable: false might fix your issue.

[douglasgsouza]

I used orphanedRowAction and it worked for me.

Parent:

@OneToMany(() => ConfiguracaoCotacaoDetalhe, detalhe => detalhe.configuracaoCotacao, {
        cascade: true
    })
    detalhes?: ConfiguracaoCotacaoDetalhe[];

Child:

    @ManyToOne(() => ConfiguracaoCotacao, config => config.detalhes, {orphanedRowAction: 'delete'})
    @JoinColumn({name: 'configuracaoCotacaoId'})
    configuracaoCotacao: ConfiguracaoCotacao;

[stefda]

Thanks @douglasgsouza, your suggestion works well! Here's the doc for anyone interested.

As far as I can tell, this solves it and the issue and can be closed?

[shindiogo]

I used orphanedRowAction and it worked for me.

Parent:

@OneToMany(() => ConfiguracaoCotacaoDetalhe, detalhe => detalhe.configuracaoCotacao, {
        cascade: true
    })
    detalhes?: ConfiguracaoCotacaoDetalhe[];

Child:

    @ManyToOne(() => ConfiguracaoCotacao, config => config.detalhes, {orphanedRowAction: 'delete'})
    @JoinColumn({name: 'configuracaoCotacaoId'})
    configuracaoCotacao: ConfiguracaoCotacao;

Works here!

Thanks, i think this can be closed.

[imnotjames]

Closing as it seems this has been resolved.

[maximosdrr]

I used orphanedRowAction and it worked for me.

Parent:

@OneToMany(() => ConfiguracaoCotacaoDetalhe, detalhe => detalhe.configuracaoCotacao, {
        cascade: true
    })
    detalhes?: ConfiguracaoCotacaoDetalhe[];

Child:

    @ManyToOne(() => ConfiguracaoCotacao, config => config.detalhes, {orphanedRowAction: 'delete'})
    @JoinColumn({name: 'configuracaoCotacaoId'})
    configuracaoCotacao: ConfiguracaoCotacao;

Still working, thanks mate !

[LapX]

I used orphanedRowAction and it worked for me.

Parent:

@OneToMany(() => ConfiguracaoCotacaoDetalhe, detalhe => detalhe.configuracaoCotacao, {
        cascade: true
    })
    detalhes?: ConfiguracaoCotacaoDetalhe[];

Child:

    @ManyToOne(() => ConfiguracaoCotacao, config => config.detalhes, {orphanedRowAction: 'delete'})
    @JoinColumn({name: 'configuracaoCotacaoId'})
    configuracaoCotacao: ConfiguracaoCotacao;

Thanks that was the solution for my problem !