Gorm How to map a []int type to a string type in SQL?

Your Question

I wrote the following code referring to this document:

func connection() *gorm.DB {
    dsn := "root:root@tcp(localhost:3306)/demo?charset=utf8mb4&parseTime=True&loc=Local"
    db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
    if err != nil {
        fmt.Println(err)
        os.Exit(-1)
    }

    return db.Debug()
}

type User struct {
    ID      int64   `json:"ID,omitempty" gorm:"primaryKey"`
    RoleIDs RoleIds `json:"roleIds,omitempty" gorm:"type:varchar(250);"`
}

func (User) TableName() string {
    return "user"
}

type RoleIds []int64

func (m *RoleIds) Scan(val interface{}) error {
    s := val.([]byte)
    ss := strings.Split(string(s), ",")
    for _, v := range ss {
        parseInt, err := strconv.ParseInt(v, 10, 64)
        if err != nil {
            return err
        }
        *m = append(*m, parseInt)
    }
    return nil
}

func (m *RoleIds) Value() (driver.Value, error) {
    if m == nil || len(*m) == 0 {
        return "", nil
    }
    strs := make([]string, len(*m))
    for i, v := range *m {
        strs[i] = strconv.FormatInt(v, 10)
    }
    return strings.Join(strs, ","), nil
}

func main() {
    conn := connection()
    u := User{
        ID:      0,
        RoleIDs: []int64{1, 2, 3, 4},
    }
    err := conn.Create(&u).Error
    if err != nil {
        println(err.Error())
    }
}

error info is :

2024/04/15 17:48:58 E:/xxxx/demo4gorm/main.go:65 Error 1241 (21000): Operand should contain 1 column(s)
[124.763ms] [rows:0] INSERT INTO `user` (`role_ids`) VALUES ((1,2,3,4))
Error 1241 (21000): Operand should contain 1 column(s)

The document you expected this should be explained

https://gorm.io/zh_CN/docs/models.html

Expected answer

Is my code implemented correctly? Are there any other implementation methods?

Comment From: ivila

@llh4github your code has bug, setting those build constraints and you will see your driver.Valuer has not been implement yet.

// import "database/sql/driver"
var _ driver.Valuer = (RoleIds)(nil)  // you will get compile error at this line
// import "database/sql"
var _ sql.Scanner = (*RoleIds)(nil)

what you should do is change your Value method

func (m RoleIds) Value() (driver.Value, error) {
    if  len(m) == 0 {
        return "", nil
    }
    strs := make([]string, len(m))
    for i, v := range m {
        strs[i] = strconv.FormatInt(v, 10)
    }
    return strings.Join(strs, ","), nil
}

and for suggestion, in your Scanner method, do not assert the value must be []byte, it could be other type in other drivers, for example, a string.