[Question] Auto associations causes strange sql errors
Mmx233 opened this issue · 2 comments
Document Link
https://gorm.io/docs/associations.html
Your Question
This query generates error sql and cannot work anyway. I use AutoMigrate to create the tables.
type UserGroups struct {
ID uint `gorm:"primarykey"`
// User.ID
UID uint `gorm:"index;index:user_group_idx,unique;not null;column:uid;"`
User User `gorm:"foreignKey:uid;constraint:OnDelete:CASCADE"`
// Group.ID
GID uint `gorm:"index;index:user_group_idx,unique;not null;column:gid"`
Group Group `gorm:"foreignKey:gid;constraint:OnDelete:RESTRICT"`
}
func (a *UserGroups) GetAllUnfrozen(tx *gorm.DB) ([]UserGroups, error) {
var t []UserGroups
return t, tx.Model(a).
Joins("INNER JOIN users ON users.id=user_groups.uid AND users.deleted_at IS NULL").
Find(&t).Error
}
the sql is
SELECT `user_groups`.`id`,`user_groups`.`uid`,`user_groups`.`gid`,`user_groups`.`user` FROM `user_groups` INNER JOIN users ON users.id=user_groups.uid AND users.deleted_at IS NULL WHERE `user_groups`.`user` IS NULL
There is a strange sql statement in it: WHERE user_groups.user IS NULL
. Either delete joins or use db.Table("table") will cause this error.
If I add gorm:"-;"
to the User User
fields, most of sql will become as expected, the strange sql will disappear. But in this function, the error is more serious:
SELECT `user_groups`.`id`,`user_groups`.`uid`,`user_groups`.`gid`,`user_groups`.`user` FROM `user_groups` INNER JOIN users ON users.id=user_groups.uid AND users.deleted_at IS NULL WHERE `user_groups`. IS NULL
The bigger error appears at the end of this sql.
Neither use Omit(clause.Associations)
nor Select("user_groups.*")
can solve the above two problems
Expected answer
Is there something wrong with the way I use gorm? How should I get my sql to bypass auto associations.
I also tried detaching the model, but the error didn't change.
type UserGroupModel struct {
ID uint `gorm:"primarykey"`
// User.ID
UID uint `gorm:"index;index:user_group_idx,unique;not null;column:uid;"`
// Group.ID
GID uint `gorm:"index;index:user_group_idx,unique;not null;column:gid"`
}
type UserGroups struct {
UserGroupModel
User User `gorm:"-;foreignKey:uid;constraint:OnDelete:CASCADE"`
Group Group `gorm:"-;foreignKey:gid;constraint:OnDelete:RESTRICT"`
}
func (a *UserGroups) GetAllUnfrozen(tx *gorm.DB) ([]UserGroupModel, error) {
var t []UserGroupModel
return t, tx.Model(a).
Joins("INNER JOIN users ON users.id=user_groups.uid AND users.deleted_at IS NULL").
Find(&t).Error
}
sql:
SELECT `user_groups`.`id`,`user_groups`.`uid`,`user_groups`.`gid` FROM `user_groups` INNER JOIN users ON users.id=user_groups.uid AND users.deleted_at IS NULL WHERE `user_groups`.` IS NULL
If I use tx.Table("user_groups")
instead based on this, sql will become normal but gorm will make an error during the scan process.
2023/05/01 18:30:00 C:/Users/Mmx/project/github.com/ncuhome/GeniusAuthoritarian/internal/db/dao/UserGroups.go:47
[40.035ms] [rows:0] SELECT `user_groups`.`id`,`user_groups`.`uid`,`user_groups`.`gid` FROM `user_groups` INNER JOIN users ON users.id=user_groups.uid AND users.deleted_at IS NULL
panic: reflect: call of reflect.Value.Field on uint Value
goroutine 1 [running]:
reflect.Value.Field({0x14d08a0?, 0xc00084c000?, 0xd53ada?}, 0x207e85845d8?)
C:/Users/Mmx/go/go1.20.1/src/reflect/value.go:1268 +0xe5
moved to go-gorm/gorm#6287