Preface

In go projects, it is often necessary to query the database. Based on previous Java development experience, many methods will be written according to the query criteria, such as:

  • GetUserByUserID
  • GetUsersByName
  • GetUsersByAge

Write a method for each query condition. This method is very good for external use, and follows strict principles to ensure that each external method interface is clear. However, internally, it should be as versatile as possible to achieve code reuse and write less code, making the code look more elegant and tidy.

Problem

When reviewing the code, the general writing method for the above three methods is

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
func GetUserByUserID(ctx context.Context, userID int64) (*User, error){
    db := GetDB(ctx)
    var user User
    if userID > 0 {
        db = db.Where(`userID = ?`, userID)
    }
    if err := db.Model(&User{}).Find(&user).Err; err != nil {
        return nil, err
    }
    
    return user, nil
}

func GetUsersByName(ctx context.Context, name string) (*User, error){
    db := GetDB(ctx)
    var users []User
    if name != "" {
        db = db.Where(`name like '%%'`, name)
    }
    if err := db.Model(&User{}).Find(&users).Err; err != nil {
        return nil, err
    }
    
    return users, nil
}

func GetUsersByAge(ctx context.Context, age int64) (*User, error){
    db := GetDB(ctx)
    var user User
    if age > 0 {
        db = db.Where(`age = ?`, age)
    }
    if err := db.Model(&User{}).Find(&user).Err; err != nil {
        return nil, err
    }
    
    return user, nil
}

When there are dozens of fields on the User table, there will be more and more similar methods above, and the code will not be reused. When there are Teacher tables, Class tables, and other tables, the above query methods need to be doubled.

The caller can also write very rigidly, with fixed parameters. When adding a query condition, either change the original function and add a parameter, so that other calls will also need to be changed; Either write a new function, which makes it more and more difficult to maintain and read.

The above is the bronze writing method. In response to this situation, the following describes several types of writing methods for silver, gold, and kings

Silver

Define the input parameter as a structure

1
2
3
4
5
type UserParam struct {
    ID int64
    Name string
    Age int64
}

Place all input parameters in the UserParam structure

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
func GetUserInfo(ctx context.Context, info *UserParam) ([]*User, error) {    
        db := GetDB(ctx)    
        db = db.Model(&User{})
        var infos []*User
        if info.ID > 0 {
            db = db.Where("user_id = ?", info.ID)    
        }
        if info.Name != "" {       
            db = db.Where("user_name = ?", info.Name)    
        }    
        if info.Age > 0 {       
            db = db.Where("age = ?", info.Age)    
        } 
        if err := db.Find(&infos).Err; err != nil {
            return nil, err
        }
        
        return infos, nil
}

When this code is written here, it is actually much better than the initial method. At least, the method in the dao layer has changed from many input parameters to one. The caller’s code can also build parameters according to its own needs, without requiring many empty placeholders. However, the existing problems are also quite obvious: there are still many empty sentences and a redundant structure has been introduced. It would be somewhat regrettable if we ended up here.

In addition, if we extend the business scenario again, and instead of using equivalent queries, we use multi valued queries or interval queries, such as querying status in (a, b), how can the above code be extended? Is it necessary to introduce a method, which is cumbersome and cumbersome for the time being. Whatever the method name is, it will make us tangle for a long time; Perhaps you can try expanding each parameter from a single value to an array, and then changing the assignment from=to in(). Using in for all parameter queries is obviously not that performance friendly.

Gold

A more advanced optimization method is to use higher order functions.

1
type Option func(*gorm.DB)

Define Option as a function whose input parameter type is * gorm.DB, and the return value is null.

Then define a higher order function for each field that needs to be queried

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
func UserID(ID int64) Option {    
    return func(db *gorm.DB) {       
        db.Where("`id` = ?", ID)    
    } 
}

func Name(name int64) Option {    
    return func(db *gorm.DB) {       
        db.Where("`name` like %?%", name)    
    } 
}

func Age(age int64) Option {    
    return func(db *gorm.DB) {       
        db.Where("`age` = ?", age)    
    } 
}

The return value is of type Option.

So that the above three methods can be combined into one method

1
2
3
4
5
6
7
8
9
10
11
func GetUsersByCondition(ctx context.Context, opts ...Option)([]*User, error) {
    db := GetDB(ctx)
    for i:=range opts {
        opts[i](db)
    }
    var users []User
    if err := db.Model(&User{}).Find(&users).Err; err != nil {
        return nil, err
    }
    return users, nil
}

Without comparison, there is no harm. By comparing with the initial method, it can be seen that the input parameters of the method have changed from multiple parameters of different types to a set of functions of the same type. Therefore, when processing these parameters, there is no need to determine the null one by one. Instead, you can directly use a for loop to handle it, which is much simpler than before.

You can also extend other query criteria, such as IN, greater than, and so on

1
2
3
4
5
6
7
8
9
10
11
func UserIDs(IDs int64) Option {    
    return func(db *gorm.DB) {       
        db.Where("`id` in (?)", IDs)    
    } 
}

func AgeGT(age int64) Option {    
    return func(db *gorm.DB) {       
        db.Where("`age` > ?", age)    
    } 
}

Moreover, this query condition is ultimately converted into a Where condition, independent of the specific table, which means that these definitions can be reused by other tables.

King

Optimization to the above methods is already possible, but the king generally continues to optimize.

The above method GetUsersByCondition can only query the User table. Can it be more general and query arbitrary tables? Sharing the GetUsersByCondition method, I found that there are two obstacles to querying any table:

  • The table name is written dead in the method

  • The return value is defined as [] * User and cannot be generalized

For the first question, we can define an Option to implement

1
2
3
4
5
func TableName(tableName string) Option {
    return func(db *grom.DB) {
        db.Table(tableName)
    }
}

For the second problem, you can use the return parameter as an input parameter and pass it in by reference

1
2
3
4
5
6
7
8
9
10
11
12
13
func GetRecords(ctx context.Context, in any, opts ...Option) {
    db := GetDB(ctx)
    for i:=range opts {
        opts[i](db)
    }
    
    return db.Find(in).Err
}

var users []User
if err := GetRecords(ctx, &users, TableName("user"), Name("Tom"), Age(18)); err != nil {
    // TODO
}

Sum Up

Here, by abstracting the Grom query conditions, it greatly simplifies the writing of DB composite queries and improves the simplicity of the code.