go-gorm/gorm.io

[Question] Auto associations causes strange sql errors

Mmx233 opened this issue · 2 comments

Mmx233 commented

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.

Mmx233 commented

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