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()

同时，goframe的ORM提供了一些常用的排序方法：

// 按照指定字段递增排序
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

8 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. harley

    为什么下面这个语句 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)

  4. 小陈

    功能好多啊, 完全记不住啊.....

  5. kim

    使用sqlserver数据库，

    model.OrderDesc("start_time").Page(req.PageNo, req.PageSize).ScanAndCount(&res.CarDistanceAlarms, &res.RecordCount, true)

    OrderDesc()方法和Page()方法链式写的话，生成的sql语句有问题，会报错但是err返回为nil，单独调用这两个方法就没问题。

    在先排序后分页的情况下，无法这样链式调用。

    1. kim

      如果不加where条件就没问题，加了where条件，有可能会报错

      1. kim

        建表语句
        CREATE TABLE [dbo].[car_distance_alarm](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[car_name] [varchar](50) NULL,
	[card_no] [varchar](50) NULL,
	[dept] [varchar](50) NULL,
	[position] [varchar](50) NULL,
	[distance] [int] NULL,
	[start_time] [datetime] NULL,
	[end_time] [datetime] NULL,
	[duration] [varchar](50) NULL,
	[car_emp_id] [varchar](50) NULL,
 CONSTRAINT [PK_car_distance_alarm] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

        查询代码：

        func (c *sCarDistanceAlarm) QueryPaging(ctx context.Context, req *vo.CarDistanceAlarmPageInput) (res *vo.CarDistanceAlarmPageOutput, err error) {

	err = dao.CarDistanceAlarm.Transaction(ctx, func(ctx context.Context, tx gdb.TX) error {
		res = new(vo.CarDistanceAlarmPageOutput)
		res.CarDistanceAlarms = make([]entity.CarDistanceAlarm, 0)
		model := dao.CarDistanceAlarm.Ctx(ctx)
		if req.Dept != "" {
			model = model.Where("dept = ?", req.Dept)
		}
		if req.CarName != "" {
			model = model.Where("car_name like ?", "%"+req.CarName+"%")
		}
		if req.CardNo != "" {
			model = model.Where("card_no like ?", "%"+req.CardNo+"%")
		}
		if req.StartTime != nil {
			model = model.Where("start_time >= ?", req.StartTime)
		}
		if req.EndTime != nil {
			model = model.Where("start_time <= ?", req.EndTime)
		}
		return model.OrderDesc("start_time").Page(req.PageNo, req.PageSize).ScanAndCount(&res.CarDistanceAlarms, &res.RecordCount, true)
	})
	return res, err
}

        测试用例一：

        无查询条件 
        func TestDBPaging(t *testing.T) {

	/*input := vo.CarDistanceAlarmPageInput{
		vo.PagingInput{
			1,
			10,
		},
		"1号材料车",
		"11",
		"车队",
		gtime.NewFromStr("2024-02-29 12:00:00"),
		gtime.NewFromStr("2024-02-29 18:00:00"),
	}*/
	input := vo.CarDistanceAlarmPageInput{
		PagingInput: vo.PagingInput{
			1,
			10,
		},
	}
	ctx := gctx.New()
	res, err := service.CarDistanceAlarm().QueryPaging(ctx, &input)
	ctx.Done()
	g.Dump(res, err)
}

        结果：

        === RUN   TestDBPaging
        2024-03-01 17:52:40.026 [DEBU] {5899dae33e9bb8177845653edb98b9bc} [  0 ms] [default] [third_party_info] [rows:1  ] [txid:1] SELECT COUNT(1) FROM car_distance_alarm
        2024-03-01 17:52:40.033 [DEBU] {5899dae33e9bb8177845653edb98b9bc} [  2 ms] [default] [third_party_info] [rows:2  ] [txid:1] SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY  start_time DESC ) as ROWNUMBER_,  id,car_name,card_no,dept,position,distance,start_time,end_time,duration,car_emp_id FROM car_distance_alarm  ) as TMP_ WHERE TMP_.ROWNUMBER_ > 0 AND TMP_.ROWNUMBER_ <= 10
        2024-03-01 17:52:40.033 [DEBU] {5899dae33e9bb8177845653edb98b9bc} [  0 ms] [default] [third_party_info] [rows:0  ] [txid:1] COMMIT

        可以正常分页查询。

        测试用例二：

        有查询条件查询 
        func TestDBPaging(t *testing.T) {

	input := vo.CarDistanceAlarmPageInput{
		vo.PagingInput{
			1,
			10,
		},
		"1号材料车",
		"11",
		"车队",
		gtime.NewFromStr("2024-02-29 12:00:00"),
		gtime.NewFromStr("2024-02-29 18:00:00"),
	}
	/*input := vo.CarDistanceAlarmPageInput{
		PagingInput: vo.PagingInput{
			1,
			10,
		},
	}*/
	ctx := gctx.New()
	res, err := service.CarDistanceAlarm().QueryPaging(ctx, &input)
	ctx.Done()
	g.Dump(res, err)
}

        结果：

        === RUN   TestDBPaging
        2024-03-01 17:57:30.141 [DEBU] {dcbe2612849bb817290aba5ebad52e94} [  4 ms] [default] [third_party_info] [rows:0  ] [txid:1] BEGIN
        2024-03-01 17:57:30.225 [DEBU] {dcbe2612849bb817290aba5ebad52e94} [  2 ms] [default] [third_party_info] [rows:1  ] [txid:1] SELECT COUNT(1) FROM car_distance_alarm WHERE (dept = '车队') AND (car_name like '%1号材料车%') AND (c      ard_no like '%11%') AND (start_time >= '2024-02-29 12:00:00') AND (start_time <= '2024-02-29 18:00:00')
        2024-03-01 17:57:30.225 [DEBU] {dcbe2612849bb817290aba5ebad52e94} [  0 ms] [default] [third_party_info] [rows:0  ] [txid:1] COMMIT

        {
            RecordCount:       0,
            CarDistanceAlarms: [],
        }
        <nil>
        --- PASS: TestDBPaging (0.10s)
        PASS

        生成的count()sql里面有一个条件字段存在很大的空格，然后查询不出来数据，但是实际上，数据库是可以查询出数据的，把空格去掉复制生成的sql去数据库执行是可以查询到数据的。

        生成的sql是这样的：

        将空格去掉后在数据库执行是可以查到数据的：