查询条件方法

最后更新于:2022-04-02 02:47:20

[TOC] > [xorm.io参考](https://gobook.io/read/gitea.com/xorm/manual-en-US/) ## 查询条件方法 ### Alias(string) ``` engine.Alias("o").Where("o.name = ?", name).Get(&order) ``` ### Where() ``` engine.Where("a = ? AND b = ?", 1, 2).Find(&beans) engine.Where(builder.Eq{"a":1, "b": 2}).Find(&beans) engine.Where(builder.Eq{"a":1}.Or(builder.Eq{"b": 2})).Find(&beans) ``` ### where 可配合其他函数 ``` config := new(IM_CONFIG) config.CFG_TYPE = "system" _, e := engine.Where(builder.In("CFG_DATA", []string{"3000", "5000"})).Get(config) //or engine.In("CFG_DATA", []string{"3000", "5000"}).Get(config) //or 字段使用实例化后的值 engine.In(config.CFG_DATA, []string{"3000", "5000"}).Get(config) ``` ### And() ``` engine.Where("user_id=?", 1).And("user_login=?", "admin").Get(&sysapp) ``` ### Asc() 与Desc() ``` engine.Asc("id").Desc("time").Find(&orders) ``` ### In() ``` // select from table where column in (1,2,3) engine.In("app_index", 4, 5).Find(sysapp) // select from table where column in (1,2,3) engine.In("column", []int{1, 2, 3}).Find() //子查询- select from table where column in (select column from table2 where a = 1) engine.In("column", builder.Select("column").From("table2").Where(builder.Eq{"a":1})).Find() ``` ### Limit() 限制获取的数目,第一个参数为条数,第二个参数表示开始位置,如果不传则为0 ### OrderBy() ### Cols() 更新或查询特定字段 ``` engine.Cols("age", "name").Get(&usr) // SELECT age, name FROM user limit 1 engine.Cols("age", "name").Find(&users) // SELECT age, name FROM user engine.Cols("age", "name").Update(&user) // UPDATE user SET age=? AND name=? //映射到 非struct cfgId :=make([]string,0) err = Engine.Table(new(SYS_CONFIG)).Cols("CFG_ID").Find(&cfgId) //获取多个字段的 非struct cfgId :=make([][]string,0) err = Engine.Table(new(SYS_CONFIG)).Cols("CFG_ID","CFG_TYPE").Find(&cfgId) ``` ## Get() 查询单条 ``` 根据条件 user := new(User) has, err := engine.Where("name=?", "xlw").Get(user) user := &User{Name:"xlw"} has, err := engine.Get(user) ``` //查询单个字段 ``` var ints int64 err := engine.Table("user").Cols("id").Get(&ints) ``` ## Find() 查询多条 ``` everyone := make([]Userinfo, 0) err := engine.Find(&everyone) pEveryOne := make([]*Userinfo, 0) err := engine.Find(&pEveryOne) ``` //查询单个字段 ``` var ints []int64 err := engine.Table("user").Cols("id").Find(&ints) ``` ## SQL() 复杂查询时,把sql 语句映射到结构体 ``` engine.SQL("select * from table").Find(&beans) ``` ## Query() ``` sql := "select * from userinfo" results, err := engine.Query(sql) //results为 []map[string][]byte ``` ## Exec() 执行Insert, Update, Delete 等操作 ``` sql = "update `userinfo` set username=? where id=?" res, err := engine.Exec(sql, "xiaolun", 1) ``` ## AllCols() 查询或更新所有字段,一般与Update配合使用,因为默认Update只更新非0,非"",非bool的字段。 ``` engine.AllCols().Id(1).Update(&user) ``` ## JOIN ``` Join("LEFT","hs_role_member", "hs_role.ROLE_ID=hs_role_member.ROLE_ID") ```
';