Go language ORM framework: construct query conditions

Construct query conditions

worm is a convenient and easy-to-use Go language ORM library. worm supports Model mode (supports structure field mapping), native SQL and SQLBuilder to operate the database, and Model mode, native SQL and SQLBuilder can be mixed.
Model method and SQL builder support chain API, and you can use functions such as Where, And, Or, ID, In, Limit, GroupBy, OrderBy, Having to construct query conditions. You can also perform associated queries between database tables through Join, LeftJoin, and RightJoin.
This article uses some examples to illustrate how to use worm to construct query conditions.

main function

 package main
import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    log "github.com/haming123/wego/dlog"
    "github.com/haming123/wego/worm"
)
func mysql_open(cnnstr string) (*sql.DB, error) {
    db, err := sql.Open("mysql", cnnstr)
    if err != nil {
        return nil, err
    }
    err = db.Ping()
    if err != nil {
        return nil, err
    }
    return db, nil
}
func main() {
    //创建数据连接池
    cnnstr := "user:passwd@tcp(127.0.0.1:3306)/dbname?charset=utf8&parseTime=True"
    db_cnn, err := mysql_open(cnnstr)
    if err != nil {
        log.Error(err)
        return
    }
    //初始化ORM
    worm.InitMysql(db_cnn)
    //显示SQL语句log
    worm.ShowSqlLog(true)
}

illustrate:

• worm code download
  go get github.com/haming123/wego
• worm.ShowSqlLog
  worm.ShowSqlLog is used to control the display of sql log. It is recommended to turn on the display switch of sql log in the test environment, so that you can see the sql statement and execution time of each database operation, which is convenient to quickly locate the problem.
• Database Support Currently, the databases supported by worm are: mysql, postgres, sqlite, and sqlserver. In this example, the mysql database is used.

Database Tables and Data Models

 //建表语句
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `passwd` varchar(32) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);

The definition of the entity class corresponding to the database table user is as follows:

 type User struct {
    Id          int64       `db:"id;autoincr"`
    Name        string      `db:"name"`
    Age         int64       `db:"age"`
    Passwd      string      `db:"passwd"`
    Created     time.Time    `db:"created;n_update"`
}
func (ent *User) TableName() string {
    return "user"
}

illustrate:

• worm uses the tag named "db" to map the database field, "db" is followed by the name of the field, autoincr is used to indicate that the field is an auto-increment ID, and n_update is used to indicate that the field cannot be used in the update statement.

Query data by ID

If the database table has the id field, you can query a data record through the ID function:

 func DemoGetById() {
    var user model.User
    _, err := worm.Model(&user).ID(1).Get()
    if err != nil {
        log.Error(err)
        return
    }
    log.Debug(user)
}
//select id,name,age,passwd,created from user where id=? limit 1

The sql log after executing the function is:

 [S] select id,name,age,passwd,created from user where id=1 limit 1
[S] DB: time=18.816ms

Query data through the Where function

The use of the Where function is similar to the Sprintf function. The first parameter of the function is the sql statement (where statement) template, and the following parameters are the value of the template variable.

 func DemoWhere() {
    var users []model.User
    err := worm.Model(&model.User{}).Where("id>? and age>?", 1, 10).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}
//对应的sql语句为：
//select id,name,age,passwd,created from user where id>? and age>?

illustrate:

• The worm placeholder is used uniformly?, and worm will automatically replace the placeholder according to the database type. For example, the postgresql database replaces ? with $1, $2...
• You can use multiple variables to query in the Where function, which is more intuitive and similar to the writing of SQL statements in database queries. However, when there are many query conditions, it is recommended to use the And and OR functions for proper segmentation to prevent errors in mapping the query variables to the values of the variables. E.g:

 func DemoWhere2() {
    var users []model.User
    err := worm.Model(&model.User{}).Where("id>?", 1).And("age>?", 10).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}
//对应的sql语句为：
//select id,name,age,passwd,created from user where id>? and age>?

• The writing method of like query, for example, to query the database records of the user's name containing: demo :

   func DemoWhereLike() {
    var users []model.User
    err := worm.Model(&model.User{}).Where("name like ?", "%demo%").Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
  }
  //对应的sql语句为：
  //select id,name,age,passwd,created from user where name like '%demo%'

XXXIf query

In some cases, we will use a variable as a query condition to query the library according to the value of the variable. For example, if the user's name is not empty, the database is queried by the user's name. The usual way of writing is as follows:

 func DemoWhereIf(name string) {
    var users []model.User
    var err error
    if name == "" {
        err = worm.Model(&model.User{}).Find(&users)
    } else {
        err = worm.Model(&model.User{}).Where("name=?", name).Find(&users)
    }
    if err != nil {
        log.Error(err)
        return
    }
}

worm provides a simpler method (provides WhereIf, AndIf, OrIf functions) to support this query requirement:

 func DemoWhereIf(name string) {
    var users []model.User
    err := worm.Model(&model.User{}).WhereIf(name != "", "name=?", name).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}

illustrate:

• The first parameter of the WhereIf function is a bool variable. If the variable is true, the query condition will be added, otherwise the query condition will be ignored.

in, not in query

Worm provides AndIn, AndNotIn, OrIn, OrNotIn functions to support in and not in queries in SQL statements. E.g:

 func DemoWhereIn() {
    var users []model.User
    err := worm.Model(&model.User{}).Where("").AndIn("id", 11, 12, 13, 14).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}
//对应的sql语句为：
select id,name,age,passwd,created from user where id in (?,?,?,?)

The second parameter of XXXIn and XXXNotIn is a variable-length parameter. You can pass in the value to be queried as a variable-length parameter, or you can put the query value into an array for query:

 func DemoWhereIn() {
    var users []model.User
    arr_id := []int64{11, 12, 13, 14}
    err := worm.Model(&model.User{}).Where("").AndIn("id", arr_id).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}

illustrate:

• If the array method is used, the variable-length parameter is one parameter, and the parameter is an array type.

Nested query statements

Worm supports nested query statements. For example, if the query is: age>10 and (name='demo1' or name='demo2') , the way to use worm is as follows:

 func DemoWhereExp() {
    var users []model.User
    sqlw := worm.SQLW("name=?", "demo1").Or("name=?", "demo2")
    err := worm.Model(&model.User{}).Where("age>?", 10).AndExp(sqlw).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}
//对应的sql语句为：
//select id,name,age,passwd,created from user where age>? and (name=? or name=?)

Limit and Offset

Limit and Offset can be used in MySQL statements to query the database, which is usually used in WEB paging queries. Worm also supports mysql's Limit and Offset statements:

 func DemoQueryPage(plen int64, pcur int64) {
    var users []model.User
    err := worm.Model(&model.User{}).Where("age>?", 10).Limit(plen).Offset(plen * pcur).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}
//对应的sql语句为：
//select id,name,age,passwd,created from user where age>? limit ?, ?

orderby query

The OrderBy function corresponds to the order by statement in the sql statement:

 func DemoQueryOrderBy(orderby string) {
    var users []model.User
    err := worm.Model(&model.User{}).Where("age>?", 10).OrderBy(orderby).Find(&users)
    if err != nil {
        log.Error(err)
        return
    }
}
//对应的sql语句为：
//select id,name,age,passwd,created from user where age>? order by created desc