Gorm Auto associations causes strange sql errors

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: WHERE user_groups. IS NULL

Neither use Omit(clause.Associations) nor Select("user_groups.*") can solve the above two problems.

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

The document you expected this should be explained

https://gorm.io/docs/associations.html

Expected answer

Is there something wrong with the way I use gorm? How should I get my sql to bypass auto associations.

Comment From: Mmx233

The issue can be solved by using dual automatic migration, but I don't think it's very elegant.

func init() {
    var e error
    if e = DB.AutoMigrate(
        &UserGroupsWithForeignKey{},
    ); e != nil {
        log.Fatalln("AutoMigration failed:", e)
    }

    if e = DB.AutoMigrate(
        &UserGroups{},
    ); e != nil {
        log.Fatalln("AutoMigration failed:", e)
    }
}

type UserGroupsWithForeignKey struct {
    UserGroups `gorm:"embedded"`
    User       User  `gorm:"foreignKey:UID;constraint:OnDelete:CASCADE"`
    Group      Group `gorm:"foreignKey:GID;constraint:OnDelete:RESTRICT"`
}

func (a *UserGroupsWithForeignKey) TableName() string {
    return "user_groups"
}

type UserGroups 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"`
}

func (a *UserGroups) GetAllUnfrozen(tx *gorm.DB) ([]UserGroups, error) {
    var t []UserGroups
    return t, tx.Model(a).Joins("INNER JOIN users u ON u.id=user_groups.uid").
        Where("u.deleted_at IS NULL").Find(&t).Error
}