Group/Order分组与排序

Group方法用于查询分组,Order方法用于查询排序。使用示例:

// SELECT COUNT(*) total,age FROM `user` GROUP BY age
g.Model("user").Fields("COUNT(*) total,age").Group("age").All()

// SELECT * FROM `student` ORDER BY class asc,course asc,score desc
g.Model("student").Order("class asc,course asc,score desc").All()

同时,goframeORM提供了一些常用的排序方法:

// 按照指定字段递增排序
func (m *Model) OrderAsc(column string) *Model
// 按照指定字段递减排序
func (m *Model) OrderDesc(column string) *Model
// 随机排序
func (m *Model) OrderRandom() *Model

使用示例:

// SELECT `id`,`title` FROM `article` ORDER BY `created_at` ASC
g.Model("article").Fields("id,title").OrderAsc("created_at").All()

// SELECT `id`,`title` FROM `article` ORDER BY `views` DESC
g.Model("article").Fields("id,title").OrderDesc("views").All()

// SELECT `id`,`title` FROM `article` ORDER BY RAND()
g.Model("article").Fields("id,title").OrderRandom().All()

Having条件过滤

Having方法用于查询结果的条件过滤。使用示例:

// SELECT COUNT(*) total,age FROM `user` GROUP BY age HAVING total>100
g.Model("user").Fields("COUNT(*) total,age").Group("age").Having("total>100").All()

// SELECT * FROM `student` ORDER BY class HAVING score>60
g.Model("student").Order("class").Having("score>?", 60).All()








Content Menu

  • No labels

4 Comments

  1. 关于group,我今天折腾了一上午,将结果写下来供大家参考。

    db := lwmain.Model.Safe()
    condition := g.Map{}
    if info.StartTime != "" {
        condition["xjsj >= ?"] = info.StartTime
    }
    
    if info.EndTime != "" {
        condition["xjsj <= ?"] = info.EndTime
    }
    
    if info.Site != "" {
        condition["dd like ?"] = "%" + info.Site + "%"
    }
    
    dbMap,_ := db.Fields("ID").Where(condition).Group(info.GroupKey).Fields("count(ID),"+info.GroupKey).All()

    其中,第一个Fields是要count的字段,第二个Fields是最外层的select字段列表。 解析出来的sql语句是:

    SELECT count(ID),`sj` FROM `lwmain` WHERE xjsj >= '2021-01-22 00:00:00' AND xjsj <= '2021-05-22 00:00:00' GROUP BY `sj`
    1. 建议:

      model := dao.lwmain
      if info.StartTime != "" {
          model = model.WhereGE(dao.lwmain.Columns.Xjsj, info.StartTime)
      }
      
      if info.EndTime != "" {
          model = model.WhereLE(dao.lwmain.Columns.Xjsj, info.EndTime)
      }
      
      if info.Site != "" {
          model = model.WhereLike(dao.lwmain.Columns.Dd, "%" + info.Site + "%")
      }
      
      dbMap, _ := model.Fields("count(ID)"+","+info.GroupKey).Group(info.GroupKey).All()
  2. 为什么下面这个语句 order排序会不起作用
    dao
    .SysMenu.Ctx(ctx).Where("status=0 AND menu_id IN(?)", menuIds).OrderDesc("sort").Scan(&menuEntitys)
  3. 想指定状态值来排序,但是m.Order()会被加上``的符号,有其他办法吗?

    m := dao.Records.Ctx(ctx)
    orderBy := " FIELD(status, 2, 1, 5, 4, 3, 6) asc "
    err = m.Order(orderBy).Scan(&list)
    
    //sql实际会变成: ORDER BY FIELD(status,`2`,`1`,`5`,`4`,`3`,6)