I am new to golang development. I have 6 parameters to pass into query using gorm. this is the select query so, based on input value we need to do filter the values. So, we need to pass the filters dynamically into query. I tried but no solution.
func GetUsers(DB *gorm.DB, Offset int, Limit int, User uibackendmodels.UserDetails) (Users []uibackendmodels.UserDetails, Err error) {
query := "SELECT userid, username, nickname, email, mobile, location, status, roleids, trsids, brandids, languagecode, createdat, createdby, modified, modifiedby" +
" FROM users WHERE 1=1 "
if User.UserName != "" || User.NickName != "" {
nameQuery := "("
if User.UserName != "" {
nameQuery = nameQuery + " username LIKE " + "'" + User.UserName + "%'"
}
if User.NickName != "" && User.UserName != "" {
nameQuery = nameQuery + " OR nickname LIKE " + "'" + User.NickName + "%'"
} else if User.NickName != "" {
nameQuery = nameQuery + " nickname LIKE " + "'" + User.NickName + "%'"
}
query = query + " AND " + nameQuery + ")"
}
if User.BrandIDs != nil && len(User.BrandIDs) > 0 {
brandIds := "("
for i := range User.BrandIDs {
if len(User.BrandIDs) == (i + 1) {
brandIds = brandIds + "'" + User.BrandIDs[i] + "' = ANY (brandids) "
} else {
brandIds = brandIds + "'" + User.BrandIDs[i] + "' = ANY (brandids) OR "
}
}
query = query + " AND " + brandIds + ")"
}
if User.TRSIDs != nil && len(User.TRSIDs) > 0 {
trsIds := "("
for i := range User.TRSIDs {
if len(User.TRSIDs) == (i + 1) {
trsIds = trsIds + "'" + User.TRSIDs[i] + "' = ANY (trsids) "
} else {
trsIds = trsIds + "'" + User.TRSIDs[i] + "' = ANY (trsids) OR "
}
}
query = query + " AND " + trsIds + ")"
}
if User.RoleIDs != nil && len(User.RoleIDs) > 0 {
roleIds := "("
for i := range User.RoleIDs {
if len(User.RoleIDs) == (i + 1) {
roleIds = roleIds + strconv.FormatInt(int64(User.RoleIDs[i]), 10) + " = ANY (roleids) "
} else {
roleIds = roleIds + strconv.FormatInt(int64(User.RoleIDs[i]), 10) + " = ANY (roleids) OR "
}
}
query = query + " AND " + roleIds + ")"
}
if User.Status != "" {
query = query + " AND status = " + "'" + EnumToString(User.Status) + "'"
}
query = query + " AND status != 'deleted' ORDER BY modified desc LIMIT " + strconv.Itoa(Limit) + " OFFSET " + strconv.Itoa(Offset)
if err := DB.Raw(query).Scan(&Users).Error; err != nil {
return nil, err
}
return Users, nil
}
I need to add parameters dynamically to above query.
In GORM, you could handle the basic inputs by enriching the Object and passing it to the function, similar to this:
// Would get the First user with Name="jinzhu" and Age="20"
db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
Whenever you cannot express a part of your statement using GORMs default functions, you can use normal SQL and Method Chaining:
// Would get the first User with Name="jinzhu" and id in (4,5,6,7)
ids := [4]int{4,5,6,7}
db.Where(&User{Name: "jinzhu"}).Where("id IN (?)", ids).First(&user)
You could also chain methods using variables
base := db.Where(&User{Name: "jinzhu"})
if someThing {
base = base.Where("id IN (?)", ids) // Adding this condition just if someThing is true
}
base.First(&user) // Query your results