Abstract
Function is the first class citizen of Go language, this paper uses a high order function way, abstract the use of GORM query DB query conditions, multiple tables of a variety of complex combination of queries abstracted into a unified method and a configuration class, improve the code concise and elegant, and can improve the efficiency of developers.
background
There is a DB table, the business needs to do the filter query according to the different fields in the table, this is a very common requirement, I believe this requirement for everyone doing business development is unavoidable. For example, we have a table that stores user information. After simplification, the table structure is as follows:
CREATE TABLE user_info (' id 'bigint unsigned NOT NULL AUTO_INCREMENT COMMENT' increment ', 'user_id' bigint NOT NULL COMMENT 'user id',' user_name 'varchar NOT NULL COMMENT' user id', 'role' int NOT NULL DEFAULT '0' COMMENT ', 'status' int NOT NULL DEFAULT '0' COMMENT' status ', InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' InnoDB ';Copy the codeThis table has several key fields: user_id, user_name, role, and status. If we want to filter by user_id, we typically write a method in the DAO layer that looks like this (all of the sample code has been omitted for brevity) :
func GetUserInfoByUid(ctx context.Context, userID int64) ([]*resource.UserInfo) { db := GetDB(ctx) db = db.Table(resource.UserInfo{}.TableName()) var infos []*resource.UserInfo db = db.Where("user_id = ?" , userID) db.Find(&infos) return infos }Copy the codeIf the business needs to query as user_name, then we need to write a similar method to query as user_name:
func GetUserInfoByName(ctx context.Context, name string) ([]*resource.UserInfo) { db := GetDB(ctx) db = db.Table(resource.UserInfo{}.TableName()) var infos []*resource.UserInfo db = db.Where("user_name = ?" , name) db.Find(&infos) return infos }Copy the codeAs you can see, the code of the two methods is very similar, and if you need to query by role or status, you have to have several more methods, resulting in a large number of similar methods. Of course, it was easy to imagine that we could solve this problem by using a single method with several inputs, so we combined the above two methods into the following method that supports filtering queries by multiple fields:
func GetUserInfo(ctx context.Context, userID int64, name string, role int, status int) ([]*resource.UserInfo) { db := GetDB(ctx) db = db.Table(resource.UserInfo{}.TableName()) var infos []*resource.UserInfo if userID > 0 { db = db.Where("user_id = ?" , userID) } if name ! = "" { db = db.Where("user_name = ?" , name) } if role > 0 { db = db.Where("role = ?" , role) } if status > 0 { db = db.Where("status = ?" , status) } db.Find(&infos) return infos }Copy the codeAccordingly, the code calling this method needs to change:
Infos := GetUserInfo(CTX, 0, UserID, "", 0, 0) Infos := GetUserInfo(CTX, 0, "", 0, Status);Copy the codeThis kind of code is very uncomfortable for both the people who write it and the people who read it. We’ve only listed four parameters here, so imagine what the code would look like if there were a dozen or 20 fields in the table that needed to be filtered. First of all, the GetUserInfo method itself has a lot of inputs, and it’s full of stuff! = 0 and! = “”, and note that 0 must not be a valid value for the field, otherwise! Lambda is equal to 0 and that’s a problem. Second, as the caller, yao is just a field according to filter query, but had to other parameters fill a 0 or a “” as placeholders, and the caller to be especially careful, because a carelessly, could fill the role position of the status, because their types are the same, the compiler will not detect any errors, It’s easy to get business bugs.
The solution
If there is a way to solve this problem, then it can only be written as bronze, and then silver, gold and Kings.
silver
A common solution to this problem is to create a new structure, place the fields of various queries in this structure, and pass this structure as an input parameter to the DAO layer’s query methods. Where the DAO methods are called, structures containing different fields are built based on each individual’s needs. In this example, we can build a UserInfo structure as follows:
type UserInfo struct {
UserID int64
Name string
Role int32
Status int32
}
Copy the codePass UserInfo as an input to the GetUserInfo method, so the GetUserInfo method looks like this:
func GetUserInfo(ctx context.Context, info *UserInfo) ([]*resource.UserInfo) { db := GetDB(ctx) db = db.Table(resource.UserInfo{}.TableName()) var infos []*resource.UserInfo if info.UserID > 0 { db = db.Where("user_id = ?" , info.UserID) } if info.Name ! = "" { db = db.Where("user_name = ?" , info.Name) } if info.Role > 0 { db = db.Where("role = ?" , info.Role) } if info.Status > 0 { db = db.Where("status = ?" , info.Status) } db.Find(&infos) return infos }Copy the codeAccordingly, the code that calls this method also needs to change:
Info := &UserInfo{UserID: UserID,} infOS := GetUserInfo(CTX, info) name, } infos := GetUserInfo(ctx, info)Copy the codeThis code is better than the original method, at least the DAO layer method has changed from many input arguments to one, and the caller’s code can also build parameters according to its own needs, without many empty placeholders. But the problem is also obvious: there are still a lot of empty sentences, and a redundant structure is introduced. It would be a shame if we ended there.
In addition, if we extend the business scenario to use multi-valued or interval queries instead of equivalent queries, such as the query status in (a, b), how does this code extend? Is it necessary to introduce a method, method cumbersome not to say, method named what will let us tangle for a long time; Perhaps you could try extending each parameter from a single value to an array, and then assigning values from = to in(). Using IN for all parameter queries is obviously not very performance-friendly.
gold
Now let’s look at the gold solution. In the above method, we introduced a redundant structure, and it was inevitable to do a lot of null-assignment in the DAO layer’s methods. So can we not introduce the superfluous structure UserInfo and also avoid these ugly nulls? The answer is yes, functional programming is a good way to solve this problem. First we need to define a function type:
type Option func(*gorm.DB)
Copy the codeDefines Option as a function that takes an input parameter of type * gorm.db and returns a null value.
Then define a function for each field in the DB table where the query needs to be filtered, and assign the value to that field, as follows:
func UserID(userID int64) Option { return func(db *gorm.DB) { db.Where("`user_id` = ?" , userID) } } func UserName(name string) Option { return func(db *gorm.DB) { db.Where("`user_name` = ?" , name) } } func Role(role int32) Option { return func(db *gorm.DB) { db.Where("`role` = ?" , role) } } func Status(status int32) Option { return func(db *gorm.DB) { db.Where("`status` = ?" , status) } }Copy the codeIn the above code, the input parameter is the filter value of a field and returns an Option function that assigns the input parameter to the current db * gorm.db object. This is the higher-order function that we mentioned at the beginning of this article, unlike our normal function, which returns a value of a simple type or a structure that encapsulates a type, this higher-order function returns a function that does something. Here I would like to add that although go language supports functional programming well, due to its current lack of support for generics, the use of higher-order functional programming does not bring more convenience to developers, so it is rare to write higher-order functions in ordinary business code. Those familiar with JAVA know that Map, Reduce, Filter and other higher-order functions in JAVA are very comfortable to use.
Now that we have this set of functions, let’s look at the dao layer query method:
func GetUserInfo(ctx context.Context, options ... func(option *gorm.DB)) ([]*resource.UserInfo) { db := GetDB(ctx) db = db.Table(resource.UserInfo{}.TableName()) for _, option := range options { option(db) } var infos []*resource.UserInfo db.Find(&infos) return infos }Copy the codeNo comparison, no damage, through compared with the first method, you can see the method into the participation by many different types of parameters into a set of the same type of function, so in dealing with these parameters, also need not empty one by one, but use a for loop is done directly, concise compared to the back of a lot.
The code to call this method is as follows:
// Query infos with userID only := GetUserInfo(CTX, userID (userID)) // Query infOS with userName only := GetUserInfo(CTX, userID) = GetUserInfo(CTX, role (role), status (status));Copy the codeWhether we use any single parameter or a combination of multiple parameters, we write the query freely, regardless of the order of parameters, concise and clear, and very readable.
Consider the extended scenario mentioned above. If we need to query multiple values, such as multiple statuses, then we just need to add a small function to Option:
func StatusIn(status []int32) Option { return func(db *gorm.DB) { db.Where("`status` in ?" , status) } }Copy the codeThe same is true for other fields or equivalent queries, and the simplicity of the code is self-evident.
The king
Can optimize to the above golden stage, in fact, has been very simple, if stop here, it is also completely possible. But if you want to take things a step further, read on!
In the above method, we have well solved the code tedious problem of multi-field combination query in a table through high-order function, but for different table query, we still need to write a query method for each table, so there is no space for further optimization? It turns out that the set of higher-order functions defined in Option has nothing to do with a table at all; it simply assigns values to gorm.db. Therefore, if we have multiple tables with common fields such as user_id, IS_deleted, create_time, and update_time in each table, then we don’t need to repeat the definition at all. We just need to define one in the Option. These functions can be reused for each table query. After further reflection, we find that Option is maintaining some stupid code, which does not need to be written manually every time. We can use script generation, scan the DB table, and generate Equal method, In method, Greater method, Less method for each field that does not repeat. Can solve all tables according to different fields do equivalent query, multi-value query, interval query.
Once the Option problem is solved, we only need to write a very simple Get method for the various combinations of queries per table. For convenience, we will paste it here again:
func GetUserInfo(ctx context.Context, options ... func(option *gorm.DB)) ([]*resource.UserInfo) { db := GetDB(ctx) db = db.Table(resource.UserInfo{}.TableName()) for _, option := range options { option(db) } var infos []*resource.UserInfo db.Find(&infos) return infos }Copy the codeThe query above is for the user_info table, but if there are other tables, we will need to write a Get method similar to this one for each table. If we take a closer look at the Get methods for each table, there are actually two differences:
- Return value types are different;
- TableName is different.
If we can solve these two problems, then we can use one method to solve all table queries. Unmarshal (json, unmarshal) returns a pointer as a parameter, so you don’t need to return the value. If the tableName is not consistent, you can add an Option method as above to solve the problem:
func TableName(tableName string) Option {
return func(db *gorm.DB) {
db.Table(tableName)
}
}
Copy the codeAfter this transformation, our DAO layer query method looks like this:
func GetRecord(ctx context.Context, in interface{}, options ... func(option *gorm.DB)) { db := GetDB(ctx) for _, option := range options { option(db) } db.Find(in) return }Copy the codeNotice that we changed the method name from GetUserInfo to GetRecord, because this method supports queries not only on the user_Info table, but also on all tables in a library. So you start with a class for each table, and then you write a bunch of query methods under each class, and now you have one method for all tables and all queries.
Then let’s look at the code that calls this method:
Var infos []* resource.userInfo GetRecord(CTX, &infos, TableName(resource.userinfo {}.tablename ()), UserID(userID), UserName(name))Copy the codeAgain, here is an example of querying the user_INFO table, specifying tableName and return type at the call point.
After such a transformation, we finally realized a simple method [GetRecord] + an automatically generated configuration class [Option] for all the tables in a library of multiple combinations of queries. The simplicity and elegance of the code has improved a bit. The catch is that the query method is called with two additional parameters, a return value variable and a tableName, which is somewhat unsightly.
conclusion
Here through the groM query conditions of the abstract, greatly simplifies the DB combined query writing method, improve the simplicity of the code. The same idea can be used to simplify the other update, INSERT, and DELETE operations, which we won’t describe here for space reasons. If you have any other ideas, please leave a comment!
reference
- Commandcenter.blogspot.com/2014/01/sel…
- Coolshell. Cn/articles / 21…
Join us
We are byte live China management team, focus on the live creation and management of the business development, give priority to, unions, user operating one-stop platform for the creation of management and incentive and operating tools, and to provide a general solutions and industry live ability, continue to create value for live business.
Push the links: job.toutiao.com/s/Lts3xLP
Internal email: [email protected]