Objection.js: how to create a many-to-many relationship on the same table?

[PierBover]

I'm trying to relate persons among themselves.

So I have the persons table:

await knex.schema.createTable('Person',(table) => {
    table.uuid('id').primary().defaultTo(knex.raw('uuid_generate_v4()'))
    table.string('firstName');
});

Then the junction table:

await knex.schema.createTable('Person_Person',(table) => {
    table.uuid('personId');
    table.uuid('parentId');
});

Then I've defined the models (I'm guessing the relationships are wrong):

class Person extends Model {
    static get tableName() {
        return 'Person';
    }

    static get relationMappings(){
        return {
            children: {
                relation: Model.HasManyRelation,
                modelClass: Person_Person,
                join: {
                    from: 'Person.id',
                    through: {
                        from: 'Person.id',
                        to: 'Person_Person.parentId'
                    },
                    to: 'Person_Person.personId'
                }
            }
        }
    }
}

class Person_Person extends Model {
    static get tableName() {
        return 'Person_Person';
    }
}

So far so good, but when I do this, nothing gets written in the Person_Person table and I get no error at all:

const dad = await Person.query().findById('81952cd3-a0b1-4a19-89c4-74da7169f78a');
const kid = await Person.query().findById('4a313960-3e84-4d32-bcb0-c30cd7294c78');

dad.$relatedQuery('children').insert(kid);

So what am I doing wrong?

[koskimas]

You are defining a ManyToManyRelation but using HasManyRelation. Objection examples and documentation contains tens of examples. You can usually find an answer to these kind of basic questions there.

So here's what you want in this case:

class Person extends Model {
  static get tableName() {
    return 'Person';
  }

  static get relationMappings() {
    return {
      children: {
        relation: Model.ManyToManyRelation,
        modelClass: Person,
        join: {
          from: 'Person.id',
          through: {
            from: 'Person_Person.parentId',
            to: 'Person_Person.personId'
          },
          to: 'Person.id'
        }
      },

      parents: {
        relation: Model.ManyToManyRelation,
        modelClass: Person,
        join: {
          from: 'Person.id',
          through: {
            from: 'Person_Person.personId',
            to: 'Person_Person.parentId'
          },
          to: 'Person.id'
        }
      }
    };
  }
}

You don't necessarily need to define a Person_Person model.

[PierBover]

Thanks for your help @koskimas .

Sorry I missed that. It's almost 5am here.

So I've changed the model to this:

children: {
    relation: Model.ManyToManyRelation,
    modelClass: Person,
    join: {
        from: 'Person.id',
        through: {
            from: 'Person_Person.personId',
            to: 'Person_Person.parentId'
        },
        to: 'Person.id'
    }
}

But now it appears that instead of writing the relationship into Person_Person, objection is trying to write it in the Person table since I'm getting this error:

insert into "Person" ("firstName", "id") values ($1, $2) returning "id" - duplicate key value violates unique constraint "Person_pkey"

And the Person_Person table is still empty.

[koskimas]

What are you trying to achieve? You already have existing rows and you simply want to create a relationship between them? If that's the case relate method is what you want. insert inserts the Person __and__ relates it to the other.

const dad = await Person.query().findById('81952cd3-a0b1-4a19-89c4-74da7169f78a');
const kid = await Person.query().findById('4a313960-3e84-4d32-bcb0-c30cd7294c78');

dad.$relatedQuery('children').relate(kid.id);

Or simply

const dad = await Person.query().findById('81952cd3-a0b1-4a19-89c4-74da7169f78a');

dad.$relatedQuery('children').relate('4a313960-3e84-4d32-bcb0-c30cd7294c78');

You could also create the Person_Person model in your original example and do this:

await Person_Person.query().insert({
  parentId: '81952cd3-a0b1-4a19-89c4-74da7169f78a',
  personId: '4a313960-3e84-4d32-bcb0-c30cd7294c78'
});

[PierBover]

Ah that was it.

Thanks a lot for your time @koskimas 👍

[OMFG5716]

What if you have an existing row and want to create a relationship between that and a new row that you need to insert?

address_book
id
....

address_book_associations
addressBookId => FK to address_book.id
associatedWithAddressBookId => FK to address_book.id