Multiple belongsToMany relationships on the same entity

[andyford]

Using Adonis 4.1, I have a mutual belongsToMany relationship between "mocs" (basically posts) and "tags". This works great.

Now I want to set up another belongsToMany relationship between mocs and collections.

The first issue I ran into was error: relation "moc_id" already exists (Postgres error 42P07) when trying to run migrations. Apparently foreign key columns have to be unique across the whole database, so since the moc_tag pivot table was already using a "moc_id" column, Postgres wouldn't let me use a "moc_id" column again for the collection_moc pivot table.

The workaround I found for this was to assign a different column name so I do this in the migration file:

table.integer('collection_moc_moc_id').unsigned().index('collection_moc_moc_id');
table.foreign('collection_moc_moc_id').references('mocs.id').onDelete('cascade');

instead of this:

table.integer('moc_id').unsigned().index('moc_id');
table.foreign('moc_id').references('mocs.id').onDelete('cascade');

...awkward, but the migration succeeded.

But I'm getting errors with .sync() and .attach() that seem to indicate that the underlying framework code is expecting a "moc_id" column on the pivot table (rather than the specified "collection_moc_moc_id" column)

sync: await moc.collections().sync(collectionIds);
error: delete from "collection_moc" where "moc_id" = $1 - column "moc_id" does not exist

attach:
await moc.collections().attach(collectionIds);
error: select "collection_id", "moc_id" from "collection_moc" where "moc_id" = $1 - column "moc_id" does not exist

Is this a bug, or am I missing something somewhere?

In my Moc model I have:

collections() {
        return this.belongsToMany('App/Models/Collection').withTimestamps();
    }

And in the Collection model I have:

mocs() {
        return this.belongsToMany('App/Models/Moc').withTimestamps();
    }

any help is appreciated

[thetutlage]

You have to define the custom key name in your relationship declaration as well. Docs does cover it https://adonisjs.com/docs/4.1/relationships#_belongs_to_many

[andyford]

Thank you, @thetutlage !

In my specific case, I had to change my the mocs() relation in my Collection model to:

mocs() {
    return this.belongsToMany('App/Models/Moc', 'collection_id', 'collection_moc_moc_id').withTimestamps();
}

and I changed the collections() relation in my Moc model to:

collections() {
    return this.belongsToMany('App/Models/Collection', 'collection_moc_moc_id').withTimestamps();
}

[andyford]

Follow up question...
I have table.timestamps(); on my collections migration.

but when collections are bulk inserted using Database.from('collections').insert(collectionsArray);, the collections saved in the collections db table have no timestamps.

any advice?

[McSneaky]

Timestamps are added by Lucid usually
But since you are accessing DB directly Lucid isn't there to add timestamps. You can set default values (NOW() or similar) to your timestamp fields or use Lucid

[andyford]

ah ok thank you @McSneaky! I didn't really get that distinction from the docs.

I switched to await moc.collections().createMany(collectionsArray); and now I have timestamps 👍🏽