Gorm预加载出现模糊列错误。

huangapple go评论80阅读模式
英文:

Gorm preload gives ambiguous column error

问题

我有以下结构体:

type Employee struct {
    EmployeeID    int64        `gorm:"primary_key;column:employee_id"`
    EmployeeCode  string       `gorm:"column:employee_code"`
    FirstName     string       `gorm:"column:first_name"`
    LastName      string       `gorm:"column:last_name"`
    DesignationID int64        `gorm:"column:designation_id;"`
    Designation   *Designation `gorm:"foreignkey:DesignationID"`
}

type Designation struct {
    DesignationID   int64  `gorm:"primary_key;column:designation_id"`
    DesignationName string `gorm:"column:designation_name"`
}

func GetEmployee(id int64) (*Employee, error) {
    db := connection.GetConn() // 获取连接
    defer db.Close()

    employee := &Employee{}
    err := db.Model(employees).Preload("Designation", func(db *gorm.DB) *gorm.DB {
        return db.Join("INNER JOIN employees ON employees.designation_id = designations.id").Order("employees.first_name DESC")
    }).Find(employee).Error
    return employee, err
}

生成的查询语句是:

SELECT * FROM employees;
SELECT * FROM designations INNER JOIN employees ON employees.designation_id = designations.id WHERE id IN (1,2) ORDER BY employees.first_name DESC;

我有一个与上述函数中写的类似的连接。在这里,我无法证明这个连接的必要性,但在实际情况中,我知道连接是必需的,并且预加载表和连接表都有一个名为id的字段。
由于两个表都有相同的列名,即id,在这种情况下,MySQL会抛出错误,错误信息为:

Error 1052: Column 'id' in where clause is ambiguous

我在gorm v1.9.16中遇到了这个问题。

我没有找到任何类似问题的资源。我们如何解决这个问题?

英文:

I have following structs

type Employee struct {
    EmployeeID        int64  `gorm:"primary_key;column:employee_id"`
    EmployeeCode      string `gorm:"column:employee_code"`
    FirstName         string `gorm:"column:first_name"`
    LastName          string `gorm:"column:last_name"`
    DesignationID     int64  `gorm:"column:designation_id;"`
    Designation       *Designation `gorm:"foreignkey:DesignationID"`
}

type Designation struct {
    DesignationID   int64  `gorm:"primary_key;column:designation_id"`
    DesignationName string `gorm:"column:designation_name"`
}

func GetEmployee(id int64) (*Employee, error) {
	db := connection.GetConn() //get connection
	defer db.Close()

	employee := &Employee{}
	err := db.Model(employees).Preload("Designation", func(db *gorm.DB) *gorm.DB {
		return db.Join("INNER JOIN employees ON employees.designation_id = designations.id").Order("employees.first_name DESC")
	}).Find(employee).Error
	return employee, err
}

The query that gets generated is

SELECT * FROM employees;
SELECT * FROM designations INNER JOIN employees ON employees.designation_id = designations.id WHERE id IN (1,2) order by employees.first_name DESC;

I have a join which is similar to what I have written in above function. Here I am not able to justfiy the join but in actual case I know that join is required and the preloading table and the join table both have id field in them.
Since both tables have same column name i.e. id in that case MYSQL throws error saying

Error 1052: Column 'id' in where clause is ambiguous

I am facing this issue in gorm v1.9.16

I have not found any resource where similar issue is mentioned.
How do we solve this issue?

答案1

得分: 1

你能尝试使用最新版本(> v1.20.x)吗?

该问题已经关闭,链接为https://github.com/go-gorm/gorm/issues/2653

这个问题似乎在新版本中已经修复,链接为https://github.com/go-gorm/gorm/commit/2ae0653af2bc19cd31f687e797b189c85f0ac3f6

英文:

Could you try with a recent version (> v1.20.x)?

The issue has been closed https://github.com/go-gorm/gorm/issues/2653

The issue seems to be fixed in the new version https://github.com/go-gorm/gorm/commit/2ae0653af2bc19cd31f687e797b189c85f0ac3f6

huangapple
  • 本文由 发表于 2023年1月4日 20:07:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/75005219.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定