How to write a gorm function for where clause with dynamic variables

user_2486083 0
Viewed 8484

I need to create a sql query :

SELECT * FROM users WHERE id = 10 AND name = "Chetan"

Now, gorm's where function looks like below,

// Where return a new relation, filter records with given conditions, accepts `map`, `struct` or `string` as conditions, refer http://jinzhu.github.io/gorm/crud.html#query
func (s *DB) Where(query interface{}, args ...interface{}) *DB {
    return s.clone().search.Where(query, args...).db
}

Which mean it accepts a query and args. Example :

dbDriver.Where("id = ?", id).First(t)

How do i dynamically pass multiple variables. Example:

SELECT * FROM users WHERE id = 10 AND name = "Chetan"
SELECT * FROM users WHERE id = 10
SELECT * FROM users WHERE gender = "male" AND name = "Chetan" AND age = "30"

Is it poosible to write a single gorm function for such dynamic SQL statements?

go-gorm go
2 Answers

You can use map[string]interface{} for coditions in .Where()

m := make(map[string]interface{})
m["id"] = 10
m["name"] = "chetan"
db.Where(m).Find(&users)

Just add your conditions in map then send inside where.

Or you can use struct in .Where(). Create a variable of struct and set those field for which you want to query and send inside where

db.Where(&User{Name: "chetan", Gender: "Male"}).First(&user)

NOTE: When query with struct, GORM will only query with those fields has non-zero value, that means if your field’s value is 0, '', false or other zero values, it won’t be used to build query conditions.

Referrence: https://gorm.io/docs/query.html#Struct-amp-Map

user_42073060

The first param of .Where() accepts string and the rest is variadic, this means you have the capability to modify the query and the values.

In the below example, I've prepared field1 & field2, and also value1 & value2 for representing the names of the fields I want to filter and their values respectively.

The values can be in any type since it's interface{}.

var field1 string = "id"
var value1 interface{} = 10

var field2 string = "age"
var value2 interface{} = "30"

dbDriver.Where(field1 " = ? AND " + field2 + " = ?", value1, value2).First(t)

Update 1

What if i am not sure what are number of parameter i will be passing? In this case we are hardcoding to two. What if that function/method passes 3 ?

One possible solution to achieve that, is by using slices to hold the criteria. You will have the control to dynamically adjust the fields and values.

fields := []string{"id = ?", "age = ?"}
values := []interface{}{10, "30"}

dbDriver.Where(strings.Join(fields, " AND "), values...).First(t)

Update 2

As per @Eklavya's comment, it's also possible to use a predefined struct object or a map instead of a string on the where clause.

db.Where(&User{Name: "jinzhu", Age: 20}).First(&user)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20 ORDER BY id LIMIT 1;

db.Where(map[string]interface{}{"name": "jinzhu", "age": 20}).Find(&users)
// SELECT * FROM users WHERE name = "jinzhu" AND age = 20;

Reference: GORM query reference

user_14679880
Related