Skip to content

[Bug] Issues while updating with Upsert with n x n relation + extra field #477

@Gustav0ar

Description

@Gustav0ar

I'm having the following issues while updating an NxN relation that has extra fields:

{ method: 'insert',
options: {},
timeout: false,
cancelOnTimeout: false,
bindings: [ 1, true, 1, 2, false, 1 ],
__knexQueryUid: 'a050bcb9-cf2a-4137-b9d2-860518490aef',
sql: 'insert into "IdiomasFalados" ("idioma_id", "oficial", "pais_id") values (?, ?, ?), (?, ?, ?) returning "idioma_id"',
returning: [ 'idioma_id' ] }
{ error: insert into "IdiomasFalados" ("idioma_id", "oficial", "pais_id") values ($1, $2, $3), ($4, $5, $6) returning "idioma_id" - insert or update on table "IdiomasFalados" violates foreign key constraint "idiomasfalados_idioma_id_foreign"
at Connection.parseE (/node_modules/pg/lib/connection.js:546:11)
at Connection.parseMessage (/node_modules/pg/lib/connection.js:371:19)
at Socket. (/node_modules/pg/lib/connection.js:114:22)
at emitOne (events.js:96:13)
at Socket.emit (events.js:188:7)
at readableAddChunk (_stream_readable.js:176:18)
at Socket.Readable.push (_stream_readable.js:134:10)
at TCP.onread (net.js:551:20)
name: 'error',
length: 292,
severity: 'ERROR',
code: '23503',
detail: 'Key (idioma_id)=(1) is not present in table "Idiomas".',
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: 'public',
table: 'IdiomasFalados',
column: undefined,
dataType: undefined,
constraint: 'idiomasfalados_idioma_id_foreign',
file: 'ri_triggers.c',
line: '3324',
routine: 'ri_ReportViolation' } null 2

My object schema:

          export class Pais extends BaseId {
          static tableName = 'Paises';
        
          static tableColumns = {
            id: 'id',
            nome: 'nome',
            moedaId: 'moeda_id'
          };
        
          static jsonSchema = {
            type: 'object',
        
            required: ['nome', 'moedaId'],
        
            properties: {
              nome: { type: 'string', minLength: 1, maxLength: 120 }
            }
          };
        
          static relationMappings: RelationMappings = {
            moeda: {
              relation: Model.BelongsToOneRelation,
              modelClass: Moeda,
              join: {
                from: 'Paises.moeda_id',
                to: 'Moedas.id'
              }
            },
            idiomas: {
              relation: Model.ManyToManyRelation,
              modelClass: Idioma,
              join: {
                from: 'Paises.id',
                through: {
                  from: 'IdiomasFalados.pais_id',
                  to: 'IdiomasFalados.idioma_id',
                  extra: ['oficial']
                },
                to: 'Idiomas.id'
              }
            }
          };
        
          nome: string;
          moedaId: number;
          moeda: Moeda;
          idiomas: Idioma[];
    }

I'm updating this object:

     { id: 1,
        nome: 'Brasil',
        moedaId: '1',
        idiomas: 
        [ { '#dbRef': 1, oficial: true },
          { '#dbRef': 2, oficial: false } ] }

Where idiomas[0] exists in the db and idiomas[1] doesn't.
For some reason it gives a constraint error that it doesn't find idiomas_id., but it exists in the db.
Also, since it's a n x n relation and doesn't have an id column, it tries to insert both relations, and it would fail since the primary key is based on 'pais_id' and 'idioma_id'.

I'm using this command to update (it works fine with insertGraph):

      const knex = Pais.knex();
      const pais = await objection.transaction(knex, async (trx: any) => Pais.query(trx).upsertGraph(model)) as any;

Any way to solve this issue in the current model?

Metadata

Metadata

Assignees

No one assigned

    Labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions