GORM Configure many to many with customs columns

Question

I have these 2 models:

type Residue struct {
    ID           int
    Name         string

    Categories []*ResidueCategory `gorm:"many2many:residue_residue_categories"`
}

type ResidueCategory struct {
    ID           int
    Name         string

    Residues []*Residue `gorm:"many2many:residue_residue_categories"`
}

I already have the residue_residue_categories table. But the columns are:

• id (int)
• residueId (int, foreign key)
• residueCategoryId (int, foreign key)

And I want to do an append association like:

db.Model(&data).Association("Categories").Append(CategoriesData)

But SQL generate is:

INSERT INTO "residue_residue_categories" ("residue_id","residue_category_id") VALUES (49,4) ON CONFLICT DO NOTHING

As you can see, the columns' names in the insert SQL are not correct.

I've already tried to configure additional fields like:

Categories []*ResidueCategory `gorm:"many2many:residue_residue_categories;foreignkey:residueId;association_foreignkey:residueCategoryId"`

Residues []*Residue `gorm:"many2many:residue_residue_categories;association_foreignkey:residueId;foreignkey:residueCategoryId"`

But weirdly, GORM throws: invalid foreign key: residueId in a simple GetById (before the append relation). How I can configure this relation with these custom columns' names?

Answer 1

I put together a small example to try to figure out your issue. First, let me share the code, then I'll walk you through all of the relevant sections of the code.

package main

import (
    "fmt"

    "gorm.io/driver/postgres"
    "gorm.io/gorm"
)

type Residue struct {
    ID   int
    Name string

    ResidueResidueCategories []*ResidueResidueCategory
}

type ResidueResidueCategory struct {
    ID                int `gorm:"primaryKey"`
    ResidueCategoryId int `gorm:"column:residueCategoryId"`
    ResidueId         int `gorm:"column:residueId"`
}

type ResidueCategory struct {
    ID   int
    Name string

    ResidueResidueCategories []*ResidueResidueCategory
}

func main() {
    dsn := "host=localhost port=54322 user=postgres password=postgres dbname=postgres sslmode=disable"
    db, err := gorm.Open(postgres.Open(dsn))
    if err != nil {
        panic(err)
    }

    db.AutoMigrate(&Residue{}, &ResidueCategory{}, &ResidueResidueCategory{})

    // insert into parent tables
    db.Create(&Residue{ID: 1, Name: "residue 1"})
    db.Create(&ResidueCategory{ID: 1, Name: "category 1"})

    // insert into join table
    db.Debug().Model(&Residue{ID: 1}).Association("ResidueResidueCategories").Append(&ResidueResidueCategory{ResidueCategoryId: 1, ResidueId: 1})

    // fetch data
    var joinTableRecords []ResidueResidueCategory
    if err := db.Model(&ResidueResidueCategory{}).Find(&joinTableRecords).Error; err != nil {
        panic(err)
    }
    for _, v := range joinTableRecords {
        fmt.Println(v)
    }
}

Now, let's take a closer look at each part.
The structs' definitions have been changed a quite. I've to insert the definition for the join table and override its columns names.

Please note that I added the definition only for completeness, you can ignore it if your join table is already present in the DB.

The other change is how I insert the data into the DB. First, you've to insert the data in the parent tables (residues and residue_categories), then you can add the association within the residue_residue_categories join table.
I added the Debug() method to show you the SQL statement sent to the DB even if you run the code without attaching the debugger.

To barely focus on the logic, I hard-coded some values within the program but it should be easy to adjust to your own logic.
Let me know if this solves your issue, thanks!