GORM configure many-to-many with custom columns

In PHP development, database operations are an inevitable part. As for the database operation framework GORM, its flexibility and powerful functions have always been favored by developers. In terms of processing many-to-many relationships, GORM provides the function of customizing column configurations, allowing developers to more precisely control the relationships between data tables. In this article, PHP editor Xigua will introduce in detail how to use GORM's custom column configuration function to handle many-to-many relationships. I hope it can help you.

Question content

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 these columns are:

• id (integer)
• residueid(int, foreign key)
• residuecategoryid(int, foreign key)

I want to make an additional association, for example:

db.model(&data).association("categories").append(categoriesdata)

But the sql generated is:

insert into "residue_residue_categories" ("residue_id","residue_category_id") values (49,4) on conflict do nothing

As you can see, the column names in the inserted sql are incorrect.

I have tried configuring other fields such as:

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 strangely, gorm throws in a simple getbyid: invalid foreign key: residualid (before appending the relationship). How do I configure the relationship with these custom column names?

Solution

I put together a small example to try to solve your problem. First, let me share the code and then I will walk you through all the relevant parts 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 definition of a structure has changed a lot. I have to insert the definition of the join table and overwrite its column names.

Please note that I am adding the definition just for completeness, if your join table already exists in the database, you can ignore it.

Another change is how I insert data into the database. First, you have to insert data in the parent tables (residues and residue_categories), then you can add associations in the residue_residue_categories join table.
I added the debug() method to show you the sql statements sent to the database, even if you run the code without a debugger attached.

To narrowly focus on the logic, I've hardcoded some values ​​into the program, but it should be easy to adjust to your own logic.
Please let me know if this solves your problem, thanks!

The above is the detailed content of GORM configure many-to-many with custom columns. For more information, please follow other related articles on the PHP Chinese website!