我正在使用GORM创建数据库模型.我有三款车型,我对它们有意见.有用户、邮箱和用户邮箱模型.我想在用户和邮箱模型之间建立很多关系,这样我就可以跟踪用户何时更改邮箱,而不必使用日志(log)表.由于UserEmail模型存在,我认为用户模型没有理由保留EmailID.否则,UserEmail只会变成一个典型的日志(log)表.
UserEmail表一次只允许任何用户设置一封邮箱.出于这个原因,我希望将UserID+DeletedAt字段作为主键.这样,只有一行DeletedAt为空.
我遇到的问题是,我正在运行的Migrator命令导致多个错误.我try 了很多不能重述一切的其他方法,但是我try 的其他方法不能正确地产生正确的外键.我想我需要在UserEmail中为EmailID和UserID设置两个外键.我也希望如果可能的话,GORM的Preload
功能应该可以工作.
我有一个迁移命令,在运行该命令时,它实际上运行以下内容:
func main() {
app.DB.Migrator().DropTable(&User{})
app.DB.Migrator().CreateTable(&User{})
app.DB.Migrator().DropTable(&Email{})
app.DB.Migrator().CreateTable(&Email{})
app.DB.Migrator().DropTable(&UserEmail{})
app.DB.Migrator().CreateTable(&UserEmail{})
}
我也try 过以不同的顺序移植每一种型号.然而,每次我都会收到错误.正确地创建了Email和UserEmail表,但是用户总是有一些错误.
2023/07/21 16:38:50 Dropping table named 'users'...
2023/07/21 16:38:50 [...]/go/pkg/mod/gorm.io/driver/mysql@v1.3.6/migrator.go:126
[error] invalid field found for struct github.com/neekla/pmapi/api/database/model.User's field Email: define a valid foreign key for relations or implement the Valuer/Scanner interface
2023/07/21 16:38:50 [...]/go/pkg/mod/gorm.io/driver/mysql@v1.3.6/migrator.go:126
[error] failed to parse value &model.User{ID:0x0, Email:model.Email{ID:0x0, Address:"", IsBanned:false, IsRegistered:false, IsVerified:false, CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:(*gorm.DeletedAt)(nil)}, PreviousEmails:[]model.Email(nil), Name:"", PasswordHash:"", CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:(*gorm.DeletedAt)(nil)}, got error invalid field found for struct github.com/neekla/pmapi/api/database/model.User's field Email: define a valid foreign key for relations or implement the Valuer/Scanner interface
2023/07/21 16:38:50 [...]/go/pkg/mod/gorm.io/driver/mysql@v1.3.6/migrator.go:128
[0.515ms] [rows:0] SET FOREIGN_KEY_CHECKS = 0;
2023/07/21 16:38:50 [...]/go/pkg/mod/gorm.io/driver/mysql@v1.3.6/migrator.go:130
[error] invalid field found for struct github.com/neekla/pmapi/api/database/model.User's field Email: define a valid foreign key for relations or implement the Valuer/Scanner interface
2023/07/21 16:38:50 Creating table named 'users'...
2023/07/21 16:38:50 [...]/api/command/commandMigrate.go:132
[error] invalid field found for struct github.com/neekla/pmapi/api/database/model.User's field Email: define a valid foreign key for relations or implement the Valuer/Scanner interface
2023/07/21 16:38:50 [...]/api/command/commandMigrate.go:132
[error] failed to parse value &model.User{ID:0x0, Email:model.Email{ID:0x0, Address:"", IsBanned:false, IsRegistered:false, IsVerified:false, CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:(*gorm.DeletedAt)(nil)}, PreviousEmails:[]model.Email(nil), Name:"", PasswordHash:"", CreatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), UpdatedAt:time.Date(1, time.January, 1, 0, 0, 0, 0, time.UTC), DeletedAt:(*gorm.DeletedAt)(nil)}, got error invalid field found for struct github.com/neekla/pmapi/api/database/model.User's field Email: define a valid foreign key for relations or implement the Valuer/Scanner interface
2023/07/21 16:38:50 [...]/api/command/commandMigrate.go:132
[error] invalid field found for struct github.com/neekla/pmapi/api/database/model.User's field Email: define a valid foreign key for relations or implement the Valuer/Scanner interface
以下是当前型号的外观,让人得以一瞥所需的功能.
func init() {
app.DB.SetupJoinTable(&User{}, "Emails", &UserEmail{})
}
// the user table should not have any EmailID column, that's what the UserEmail table is for, but it would be nice to still be able to preload the email values
// Email field should be the email in UserEmails that has NULL for for DeletedAt
// PreviousEmails field should be all the emails in UserEmails that have a non-NULL DeletedAt
type User struct {
ID uint `json:"id" gorm:"primarykey"`
Email Email `json:"email"`
PreviousEmails []Email `json:"previous_emails"`
Name string `json:"name" gorm:"type:varchar(255);not null"`
PasswordHash string `json:"password_hash,omitempty" gorm:"type:binary(60);not null"`
CreatedAt time.Time `json:"created_at" gorm:"type:DATETIME;default:CURRENT_TIMESTAMP;not null"`
UpdatedAt time.Time `json:"updated_at" gorm:"type:DATETIME"`
DeletedAt *gorm.DeletedAt `json:"deleted_at" gorm:"type:DATETIME;index"`
}
func (User) TableName() string {
return "users"
}
// emails do not get deleted, they are permanently stored
type Email struct {
ID uint `json:"id" gorm:"primarykey"`
Address string `json:"address" gorm:"type:varchar(320);unique;not null"`
IsBanned bool `json:"is_banned" gorm:"type:bit;default:0;not null"`
IsRegistered bool `json:"is_registered" gorm:"type:bit;default:0;not null"`
IsVerified bool `json:"is_verified" gorm:"type:bit;default:0;not null"`
CreatedAt time.Time `json:"created_at" gorm:"type:DATETIME;default:CURRENT_TIMESTAMP;not null"`
UpdatedAt time.Time `json:"updated_at" gorm:"type:DATETIME"`
}
func (Email) TableName() string {
return "emails"
}
// when a user changes their email, the old one gets soft deleted
type UserEmail struct {
EmailID uint `json:"email_id"`
UserID uint `json:"user_id" gorm:"primarykey"`
CreatedAt time.Time `json:"created_at" gorm:"type:DATETIME;default:CURRENT_TIMESTAMP;not null"`
DeletedAt *gorm.DeletedAt `json:"deleted_at" gorm:"primarykey;type:DATETIME"`
}
func (UserEmail) TableName() string {
return "user_emails"
}
我如何才能达到预期的效果?