- 表结构和数据
user表
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`user_key` bigint(20) NOT NULL,
`account` char(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
为了测试将user_key和id写入同样的值
数据:
+----+----------+---------+
| id | user_key | account |
+----+----------+---------+
| 1 | 1 | user-1 |
+----+----------+---------+
profile 表
CREATE TABLE `profile` (
`id` bigint(20) NOT NULL,
`profile_key` bigint(20) NOT NULL,
`desc` char(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
为了测试将profile_key和id写入同样的值
数据:
+----+-------------+-----------+
| id | profile_key | desc |
+----+-------------+-----------+
| 2 | 2 | profile-1 |
| 3 | 3 | profile-2 |
+----+-------------+-----------+
中间表user_profile用来记录多对多关系,一个user可以有多个profile,一个profile也可以被多个user关联。
CREATE TABLE `user_profile` (
`id` bigint(20) NOT NULL,
`profile_id` bigint(20) NOT NULL,
`user_id` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
数据:
+----+------------+---------+
| id | profile_id | user_id |
+----+------------+---------+
| 4 | 2 | 1 |
| 5 | 3 | 1 |
+----+------------+---------+
-
golang代码
package test_service import ( "encoding/json" "fmt" "gorm.io/driver/mysql" "gorm.io/gorm" "gorm.io/gorm/logger" ) type User struct { Id int `json:"id" gorm:"id"` UserKey int `json:"user_key" gorm:"user_key"` Account string `json:"account" gorm:"account"` // 默认用user表的id,去关联中间表 user_profile 的user_id, 用profile表的id去关联中间表 user_profile 的 profile_id Profiles []Profile `gorm:"many2many:user_profile;joinForeignKey:user_id;joinReferences:profile_id;" json:"profiles"` // 有些时候我们关联并不是用自身的id字段去关关联,通过foreignKey 指定 user_key,那么将用user 表的 user_key字段对应,user_profile 表的user_id字段 // 通过指定 references:profile_key ,将用 profile 表的 profile_key 字段对应,user_profile 表的 profile_id 字段 // 关联的字段,直接指定字段名的方式是可以的 AllProfiles []Profile `gorm:"many2many:user_profile;foreignKey:user_key;joinForeignKey:user_id;references:profile_key;joinReferences:profile_id;" json:"all_profiles"` // 关联的字段,指定结构体中的属性名也可以,many2many 需要指定的中间表名,指定结构体转成表名会默认加 s,如果实际表名没有s,会报错 //AllProfiles []Profile `gorm:"many2many:user_profile;foreignKey:UserKey;joinForeignKey:UserId;references:ProfileKey;joinReferences:ProfileId;" json:"all_profiles"` } func (*User) TableName() string { return "user" } type Profile struct { Id int `json:"id" gorm:"id"` ProfileKey int `json:"profile_key" gorm:"profile_key"` Desc string `json:"desc" gorm:"desc"` } func (*Profile) TableName() string { return "profile" } type UserProfile struct { Id int `json:"id" gorm:"id"` ProfileId int `json:"profile_id" gorm:"profile_id"` UserId int `json:"user_id" gorm:"user_id"` } func (*UserProfile) TableName() string { return "user_profile" } var db *gorm.DB func init() { addr := "127.0.0.1:3306" user := "user" pass := "pass" name := "test_1" dsn := "%s:%s@tcp(%s)/%s?charset=utf8mb4&parseTime=True&loc=Local" dsn = fmt.Sprintf(dsn, user, pass, addr, name) db, _ = gorm.Open(mysql.Open(dsn), &gorm.Config{ Logger: logger.Default.LogMode(logger.Info), }) } func ManyToMany() { var users []User db.Preload("Profiles").Preload("AllProfiles").Where("id", 1).Find(&users) by, _ := json.Marshal(users) fmt.Println(string(by)) }
-
调用ManyToMany 方法打印的json
[ { "id": 1, "user_key": 1, "account": "user-1", "profiles": [ { "id": 2, "profile_key": 2, "desc": "profile-1" }, { "id": 3, "profile_key": 3, "desc": "profile-2" } ], "all_profiles": [ { "id": 2, "profile_key": 2, "desc": "profile-1" }, { "id": 3, "profile_key": 3, "desc": "profile-2" } ] } ]
-
实际执行的sql
# 根据id找到user
SELECT * FROM `user` WHERE `id` = 1;
# 根据关联关系找中间表
SELECT * FROM `user_profile` WHERE `user_profile`.`user_id` = 1;
# 根据中间表记录找到具体的profile数据
SELECT * FROM `profile` WHERE `profile`.`id` IN (2,3);
标签:profile,gorm,GORM,golang,many2many,json,user,key,id
From: https://www.cnblogs.com/jing1024/p/18110423