The ORM component provides some commonly used conditional query methods, and the conditional methods support multiple data type inputs.

func (m *Model) Where(where interface{}, args...interface{}) *Model
func (m *Model) Wheref(format string, args ...interface{}) *Model
func (m *Model) WherePri(where interface{}, args ...interface{}) *Model
func (m *Model) WhereBetween(column string, min, max interface{}) *Model
func (m *Model) WhereLike(column string, like interface{}) *Model
func (m *Model) WhereIn(column string, in interface{}) *Model
func (m *Model) WhereNull(columns ...string) *Model
func (m *Model) WhereLT(column string, value interface{}) *Model
func (m *Model) WhereLTE(column string, value interface{}) *Model
func (m *Model) WhereGT(column string, value interface{}) *Model
func (m *Model) WhereGTE(column string, value interface{}) *Model

func (m *Model) WhereNotBetween(column string, min, max interface{}) *Model
func (m *Model) WhereNotLike(column string, like interface{}) *Model
func (m *Model) WhereNotIn(column string, in interface{}) *Model
func (m *Model) WhereNotNull(columns ...string) *Model

func (m *Model) WhereOr(where interface{}, args ...interface{}) *Model
func (m *Model) WhereOrBetween(column string, min, max interface{}) *Model
func (m *Model) WhereOrLike(column string, like interface{}) *Model
func (m *Model) WhereOrIn(column string, in interface{}) *Model
func (m *Model) WhereOrNull(columns ...string) *Model
func (m *Model) WhereOrLT(column string, value interface{}) *Model
func (m *Model) WhereOrLTE(column string, value interface{}) *Model
func (m *Model) WhereOrGT(column string, value interface{}) *Model
func (m *Model) WhereOrGTE(column string, value interface{}) *Model

func (m *Model) WhereOrNotBetween(column string, min, max interface{}) *Model
func (m *Model) WhereOrNotLike(column string, like interface{}) *Model
func (m *Model) WhereOrNotIn(column string, in interface{}) *Model
func (m *Model) WhereOrNotNull(columns ...string) *Model

Below, we provide a brief introduction to several commonly used methods; other conditional query methods are similar in usage.

Where/WhereOr Query Conditions

Introduction

These two methods are used to pass query condition parameters, and the supported parameters can be any string/map/slice/struct/*struct type.

It is recommended to use a string parameter method for Where condition parameters (using ? as a placeholder for preprocessing) because map/struct types as query parameters cannot guarantee order, and in some cases (the database may help you automatically optimize query indexes), the order of the database index and the order of your query condition has a certain relationship.

When using multiple Where methods to connect query conditions, the conditions are connected using And. In addition, when multiple query conditions exist, gdb will default to enclosing each condition in () brackets, allowing for friendly support for query condition grouping.

Example usage:

// WHERE `uid`=1
Where("uid=1")
Where("uid", 1)
Where("uid=?", 1)
Where(g.Map{"uid" : 1})
// WHERE `uid` <= 1000 AND `age` >= 18
Where(g.Map{
    "uid <=" : 1000,
    "age >=" : 18,
})

// WHERE (`uid` <= 1000) AND (`age` >= 18)
Where("uid <=?", 1000).Where("age >=?", 18)

// WHERE `level`=1 OR `money`>=1000000
Where("level=? OR money >=?", 1, 1000000)

// WHERE (`level`=1) OR (`money`>=1000000)
Where("level", 1).WhereOr("money >=", 1000000)

// WHERE `uid` IN(1,2,3)
Where("uid IN(?)", g.Slice{1,2,3})

Example using struct parameters, where the orm tag is used to specify the mapping relationship between struct attributes and table fields:

type Condition struct{
    Sex int `orm:"sex"`
    Age int `orm:"age"`
}
Where(Condition{1, 18})
// WHERE `sex`=1 AND `age`=18

Example Usage

Where + string, using string and preprocessing as condition parameters.

// Query multiple records with Limit pagination
// SELECT * FROM user WHERE uid>1 LIMIT 0,10
g.Model("user").Where("uid > ?", 1).Limit(0, 10).All()

// Using the Fields method to specify query fields
// Default query is * when Fields method is not used to specify query fields
// SELECT uid,name FROM user WHERE uid>1 LIMIT 0,10
g.Model("user").Fields("uid,name").Where("uid > ?", 1).Limit(0, 10).All()

// Supporting multiple Where condition parameter types
// SELECT * FROM user WHERE uid=1 LIMIT 1
g.Model("user").Where("uid=1").One()
g.Model("user").Where("uid", 1).One()
g.Model("user").Where("uid=?", 1).One()

// SELECT * FROM user WHERE (uid=1) AND (name='john') LIMIT 1
g.Model("user").Where("uid", 1).Where("name", "john").One()
g.Model("user").Where("uid=?", 1).Where("name=?", "john").One()

// SELECT * FROM user WHERE (uid=1) OR (name='john') LIMIT 1
g.Model("user").Where("uid=?", 1).WhereOr("name=?", "john").One()

Where + slice, preprocessing parameters can be directly provided through the slice parameter.

// SELECT * FROM user WHERE age>18 AND name like '%john%'
g.Model("user").Where("age>? AND name like ?", g.Slice{18, "%john%"}).All()

// SELECT * FROM user WHERE status=1
g.Model("user").Where("status=?", g.Slice{1}).All()

Where + map, using any map type to pass condition parameters.

// SELECT * FROM user WHERE uid=1 AND name='john' LIMIT 1
g.Model("user").Where(g.Map{"uid" : 1, "name" : "john"}).One()

// SELECT * FROM user WHERE uid=1 AND age>18 LIMIT 1
g.Model("user").Where(g.Map{"uid" : 1, "age>" : 18}).One()

Where + struct/*struct, struct tags support orm/json, mapping properties to field names.

type User struct {
    Id       int    `json:"uid"`
    UserName string `orm:"name"`
}
// SELECT * FROM user WHERE uid =1 AND name='john' LIMIT 1
g.Model("user").Where(User{ Id : 1, UserName : "john"}).One()

// SELECT * FROM user WHERE uid =1 LIMIT 1
g.Model("user").Where(&User{ Id : 1}).One()

The query conditions above are relatively simple. Let's look at a more complex query example.

condition := g.Map{
    "title like ?"         : "%Jiuzhai%",
    "online"               : 1,
    "hits between ? and ?" : g.Slice{1, 10},
    "exp > 0"              : nil,
    "category"             : g.Slice{100, 200},
}
// SELECT * FROM article WHERE title like '%Jiuzhai%' AND online=1 AND hits between 1 and 10 AND exp > 0 AND category IN(100,200)
g.Model("article").Where(condition).All()

Wheref Formatted Condition String

In some scenarios, entering a conditional statement with strings often requires the use of fmt.Sprintf to format the condition (note to use placeholders in the string instead of directly formatting the variable), so a convenient method combining Where+fmt.Sprintf, Wheref, is provided. Usage example:

// WHERE score > 100 and status in('succeeded','completed')
Wheref(`score > ? and status in (?)`, 100, g.Slice{"succeeded", "completed"})

WherePri Supports Primary Key Query Conditions

The WherePri method functions the same way as Where, but it provides intelligent recognition of table primary keys and is commonly used for convenient data queries based on primary keys. Suppose the primary key of the user table is uid, let's see the difference between Where and WherePri:

// WHERE `uid`=1
Where("uid", 1)
WherePri(1)

// WHERE `uid` IN(1,2,3)
Where("uid", g.Slice{1,2,3})
WherePri(g.Slice{1,2,3})

As you can see, when using the WherePri method and the given parameter is a single basic type or a slice type, it will be recognized as the value of the primary key query condition.

WhereBuilder Complex Condition Combinations

WhereBuilder is used to generate complex Where conditions.

Object Creation

We can use the Builder method of Model to generate a WhereBuilder object. The method is defined as follows:

// Builder creates and returns a WhereBuilder.
func (m *Model) Builder() *WhereBuilder

Usage Example

// SELECT * FROM `user` WHERE `id`=1 AND `address`="USA" AND (`status`="active" OR `status`="pending")
m := g.Model("user")
all, err := m.Where("id", 1).Where("address", "USA").Where(
    m.Builder().Where("status", "active").WhereOr("status", "pending"),
).All()

Note: 0=1 Condition Triggered by Empty Array Condition

Let's look at an example:

SQL1:

m := g.Model("auth")
m.Where("status", g.Slice{"permitted", "inherited"}).Where("uid", 1).All()
// SELECT * FROM `auth` WHERE (`status` IN('permitted','inherited')) AND (`uid`=1)

SQL2:

m := g.Model("auth")
m.Where("status", g.Slice{}).Where("uid", 1).All()
// SELECT * FROM `auth` WHERE (0=1) AND (`uid`=1)

As you can see, when the given array condition is an empty array, the resulting SQL has a 0=1 invalid condition. Why is that?

When developers do not explicitly declare the intention to filter empty array conditions, ORM does not automatically filter empty array conditions to avoid program logic bypassing SQL restriction conditions, which could cause unpredictable business issues. If the developer determines that the SQL restriction condition can be filtered, they can explicitly call the OmitEmpty/OmitEmptyWhere methods to perform empty condition filtering, as shown below:

m := g.Model("auth")
m.Where("status", g.Slice{}).Where("uid", 1).OmitEmpty().All()
// SELECT * FROM `auth` WHERE `uid`=1