Gorm how to get insert id when we use Raw for complex insert sql

Your Question

how to get insert id when we use Raw for complex insert sql?

err := dl.db.Transaction(func(tx *gorm.DB) error {
        sql := "INSERT INTO form (uuid, history, status) SELECT concat(?, LPAD(t.n+1, 3, 0)), ?, ?, ? from (select count(*) as n from form where uuid like ?) t"
        err := tx.Raw(sql, prefix, history, status, prefix+"%").Error     // db insert success but how to get the insert id ?
        if err != nil {
            return err
        }

                // i tried this
        sql = `select LAST_INSERT_ID()`
        err = tx.Exec(sql).Scan(&req.ID).Error.    // the request id always 0
        if err != nil {
            return err
        }

        // operation on other object
        .....

        return nil
    })

The document you expected this should be explained

Expected answer

get the inserted id

Comment From: tr1v3r

Seems like a issue for GORM?

Comment From: shyandsy

Seems like a issue for GORM?

yes looks like there is no way to get id in this case

Comment From: mirusky

@shyandsy I suppose you're using postgres or oracle based DBSM, so probably you need to put RETURNING statement, something like:

var id int
sql := "INSERT INTO form (uuid, history, status) SELECT concat(?, LPAD(t.n+1, 3, 0)), ?, ?, ? from (select count(*) as n from form where uuid like ?) t RETURNING id"
err := tx.Raw(sql, prefix, history, status, prefix+"%").Scan(&id).Error

Comment From: jinzhu

yes, or you can use the generic db interface https://gorm.io/docs/generic_interface.html