英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论