SQL COALESCE function does not work with GORM

php editor Xinyi is here to introduce to you the COALESCE function in SQL. The COALESCE function is used to return the first non-NULL value in the parameter list. However, it should be noted that when using GORM (Go language ORM framework), the COALESCE function may cause some problems. This is because GORM processes query statements differently from traditional SQL statements. Therefore, if you encounter problems with the COALESCE function when using GORM, you may want to consider other solutions.

Question content

db.model(&domain.products{}).where("product_code", product.product_code).
updates(map\[string\]interface{}{
"product_image": gorm.expr("coalesce(?, products.product_image)", product.product_image),
"size":          gorm.expr("coalesce(?, products.size)", product.size),
"color":         gorm.expr("coalesce(?, products.color)", product.color),
"unit_price":    gorm.expr("coalesce(?, products.unit_price)", product.unit_price),
"stock":         gorm.expr("coalesce(?, products.stock)", product.stock),
})

This is a gorm query to manage null insertions when we update the table. If the new value (incoming value) is null, I want to update the table by retaining the existing values ​​in the table. But in my code, the table updates normally, which means both null and non-null values ​​update normally. I hope someone can help me

I also tried gorm raw query. But it doesn't work either

ar.DB.Exec("UPDATE products SET size = COALESCE(?, size) WHERE product_code = ?", product.Size, product.Product_Code)

Solution

coalesce Returns the first parameter that is not nil.

To do this, you need to provide a pointer. However, as you can see from the code snippet in the comments, no pointers are provided. Therefore, all specified fields will be updated.

Specifically, your products (or whatever name it is called in your program) type should look more like this:

package domain

type products struct {
    product_code  int `gorm:"not null"`
    product_image *string
    size          *int
    color         *string
    unit_price    *float64
    stock         *int
}

Please note that fields are defined as pointers.

Then it would be used like this:

newStock := 12
prod := domain.Products{    
    product_code: 42,   
    product_image: nil,     
    size: nil,  
    color: nil,     
    unit_price: nil,    
    stock: &newStock, 
}

Sine all fields are nil except stock, only stock will be updated in the database.

The above is the detailed content of SQL COALESCE function does not work with GORM. For more information, please follow other related articles on the PHP Chinese website!