gorm
gorm copied to clipboard
go-gorm
Make LIKE query using struct fields
Your Question
I want to run a LIKE query using gorm. Following the documentation, the only way is to use raw SQL query as explained in this page: https://gorm.io/docs/query.html --> db.Where("name LIKE ?", "%jin%").Find(&users)
The problem is that using this command I need to use real database field names. So, the advantage to use an ORM is erased (different DB could use different naming conventions, like uppercase or other).
How can I run LIKE queries using struct fields and no real database field names?
Hi,
i have mapped db fields with struct fields using this:
type BaseDAO struct {
FieldCache map[string]string
}
func NewBaseDAO(model interface{}) BaseDAO {
dao := BaseDAO{
FieldCache: make(map[string]string),
}
s, err := schema.Parse(model, &sync.Map{}, schema.NamingStrategy{})
if err != nil {
panic("Failed to setup basedao field name cache")
}
for _, field := range s.Fields {
dao.FieldCache[field.Name] = field.DBName
}
return dao
}
func (dao BaseDAO) GetByFilter(ctx fw.Context, output interface{}, filters map[string]interface{}) error {
db := ctx.DB().(*gorm.DB)
for k, v := range filters {
dbName := dao.FieldCache[k]
switch v.(type) {
case []string:
db = db.Where(dbName+" IN ?", v)
case []uint:
db = db.Where(dbName+" IN ?", v)
case []int:
db = db.Where(dbName+" IN ?", v)
case int:
db = db.Where(dbName+" = ?", v)
case uint:
db = db.Where(dbName+" = ?", v)
case time.Time:
db = db.Where(dbName+" = ", v)
default:
db = db.Where(dbName+" LIKE ?", v)
}
}
return db.Find(output).Error
}
You can invoke NewBaseDAO with:
xx := NewBaseDAO(myEntity{})
and use the struct field names in the GetByFilter input map
PS: actually i have tested it only for postgres. I hope it works for every db :)
Hi @lgtti ,
Thank you for your solution, luckily I use postgres too rn :)
