Gorm preload gives ambiguous column error-CodePudding

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?

CodePudding user response：

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