Gorm About the many-to-many relationship doc. The example is not detailed.

Your Question

My question is about how to customize jointable. The example shown in doc is

type Person struct {
  ID        int
  Name      string
  Addresses []Address `gorm:"many2many:person_address;"`
}

type Address struct {
  ID   uint
  Name string
}

type PersonAddress struct {
  PersonID  int `gorm:"primaryKey"`
  AddressID int `gorm:"primaryKey"`
  CreatedAt time.Time
  DeletedAt gorm.DeletedAt
}

func (PersonAddress) BeforeCreate(db *gorm.DB) error {
  // ...
}

// 修改 Person 的 Addresses 字段的连接表为 PersonAddress
// PersonAddress 必须定义好所需的外键，否则会报错
err := db.SetupJoinTable(&Person{}, "Addresses", &PersonAddress{})

It points out PersonAddress 必须定义好所需的外键，否则会报错. I am confused how to define foreignKey in this example.

Now this is my own test unit try to use many-to-many relationship

type IP struct {
    ID      int    `json:"ip_id" gorm:"column:ip_id; primaryKey"`
    Title   string `json:"ip_title" gorm:"column:ip_title"`
    Content string `json:"ip_content" gorm:"column:ip_content"`
    Status  string `json:"ip_status" gorm:"column:ip_status"`

    Patents  []*Patent  `json:"ip_patents" gorm:"foreignKey:patent_ip"`

    Managers []*Manager `json:"ip_managers" gorm:"many2many:ip_managedby_table; joinForeignKey:ip_managedby_manager_id;"`
}

type Manager struct {
    ID   int    `json:"manager_id" gorm:"column:manager_id; primaryKey"`
    Name string `json:"manager_name" gorm:"column:manager_name"`
    IPs  []*IP  `json:"manager_ips" gorm:"many2many:ip_managedby_table; joinForeignKey:ip_managedby_ip_id;"`
}

type IPManagedBy struct {
    IP      int `json:"ip_managedby_ip_id" gorm:"column:ip_managedby_ip_id; primaryKey; foreignKey:ip_id; references:IP(ip_id)"`
    Manager int `json:"ip_managedby_manager_id" gorm:"column:ip_managedby_manager_id; primaryKey; foreignKey:manager_id; references:Manager(manager_id)"`
}

Now I create these three table

    db.Migrator().DropTable(&IPManagedBy{})
    db.Migrator().DropTable(&Manager{})
    db.Migrator().DropTable(&IP{})

    db.AutoMigrate(&IPManagedBy{})
    db.AutoMigrate(&IP{})
    db.AutoMigrate(&Manager{})

The IP table and Manager table is expected.

However, as for IPManagedBy table, there are two additional schema.

+-------------------------+--------+------+-----+---------+-------+
| Field                   | Type   | Null | Key | Default | Extra |
+-------------------------+--------+------+-----+---------+-------+
| ip_managedby_ip_id      | bigint | NO   | PRI | NULL    |       |
| ip_managedby_manager_id | bigint | NO   | PRI | NULL    |       |
| ip_id                   | bigint | YES  | MUL | NULL    |       |
| manager_id              | bigint | YES  |     | NULL    |       |
+-------------------------+--------+------+-----+---------+-------+

I check the log. the log is like this

2023/04/12 22:22:24 /home/zreal/toyProj/IPM_Internal/sql_test.go:119 SLOW SQL >= 200ms
[805.270ms] [rows:0] DROP TABLE IF EXISTS `ip_managedby_table` CASCADE

2023/04/12 22:22:25 /home/zreal/toyProj/IPM_Internal/sql_test.go:120 SLOW SQL >= 200ms
[981.382ms] [rows:0] DROP TABLE IF EXISTS `manager_table` CASCADE

2023/04/12 22:22:25 /home/zreal/toyProj/IPM_Internal/sql_test.go:121 SLOW SQL >= 200ms
[491.566ms] [rows:0] DROP TABLE IF EXISTS `ip_table` CASCADE

2023/04/12 22:22:26 /home/zreal/toyProj/IPM_Internal/sql_test.go:123 SLOW SQL >= 200ms
[845.501ms] [rows:0] CREATE TABLE `ip_managedby_table` (`ip_managedby_ip_id` bigint,`ip_managedby_manager_id` bigint,PRIMARY KEY (`ip_managedby_ip_id`,`ip_managedby_manager_id`))

2023/04/12 22:22:27 /home/zreal/toyProj/IPM_Internal/sql_test.go:124 SLOW SQL >= 200ms
[838.488ms] [rows:0] CREATE TABLE `ip_table` (`ip_id` bigint AUTO_INCREMENT,`ip_title` longtext,`ip_content` longtext,`ip_status` longtext,PRIMARY KEY (`ip_id`))

2023/04/12 22:22:28 /home/zreal/toyProj/IPM_Internal/sql_test.go:124 SLOW SQL >= 200ms
[771.384ms] [rows:0] CREATE TABLE `manager_table` (`manager_id` bigint AUTO_INCREMENT,`manager_name` longtext,PRIMARY KEY (`manager_id`))

2023/04/12 22:22:28 /home/zreal/toyProj/IPM_Internal/sql_test.go:124 SLOW SQL >= 200ms
[406.072ms] [rows:0] ALTER TABLE `ip_managedby_table` ADD `ip_id` bigint

2023/04/12 22:22:30 /home/zreal/toyProj/IPM_Internal/sql_test.go:124 SLOW SQL >= 200ms
[1617.418ms] [rows:0] ALTER TABLE `ip_managedby_table` ADD CONSTRAINT `fk_ip_managedby_table_manager` FOREIGN KEY (`ip_managedby_ip_id`) REFERENCES `manager_table`(`manager_id`)

2023/04/12 22:22:32 /home/zreal/toyProj/IPM_Internal/sql_test.go:124 SLOW SQL >= 200ms
[2016.046ms] [rows:0] ALTER TABLE `ip_managedby_table` ADD CONSTRAINT `fk_ip_managedby_table_ip` FOREIGN KEY (`ip_id`) REFERENCES `ip_table`(`ip_id`)

2023/04/12 22:22:32 /home/zreal/toyProj/IPM_Internal/sql_test.go:125 SLOW SQL >= 200ms
[539.352ms] [rows:0] ALTER TABLE `ip_managedby_table` ADD `manager_id` bigint

There are some operation to alter table.

I want to know the complete process to add foreignKey constrain when customizing jointable.

The document you expected this should be explained

Expected answer

Comment From: Zrealshadow

I fix above problem, Maybe we should add some more detailed example for many-to-many usage, especially involving all features like overwrite foreignkey and customize jointable

Comment From: a631807682

Thanks for your feedback, are you interested in creating a PR for it? Documentation project is at https://github.com/go-gorm/gorm.io

Comment From: github-actions[bot]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days