Session.Count 在有 group by 时处理的问题 #1910

New Issue

yoke_snowwolf · 2021-05-24T13:35:51Z

yoke_snowwolf commented

2021-05-24 13:35:51 +00:00

GenCountSQL 生成的 sqlStr 存在直接的 group by 时，Session.Count 就会改变原语义了，

嗯，其实是应该调整 GenCountSQL 或 genSelectSQL，

这里是我一个比较粗暴的简单处理修改，因为并不非常完善也不能完全适用所有情况，所以只是扩展了一个函数，大部分时候倒也是够用了，仅供参考

// Count counts the records. bean's non-empty fields
// are conditions.
func (session *Session) CountExt(bean ...interface{}) (int64, error) {
	if session.isAutoClose {
		defer session.Close()
	}

	sqlStr, args, err := session.statement.GenCountSQL(bean...)
	if err != nil {
		return 0, err
	}

	sqlStrLower := strings.ToLower(sqlStr)
	groupIndex := strings.LastIndex(sqlStrLower, "group")
	if groupIndex >= 0 {
		groupTailStr := sqlStrLower[groupIndex + len("group"):]
		byIndex := strings.Index(groupTailStr, "by")
		if byIndex >= 0 && byIndex < 5 {
			sqlStr = strings.Replace(sqlStr, "count(*)", "1 as t", -1)
			sqlStr = "select count(*) from (" + sqlStr + ") as t"
		}
	}

	var total int64
	err = session.queryRow(sqlStr, args...).Scan(&total)
	if err == sql.ErrNoRows || err == nil {
		return total, nil
	}

	return 0, err
}

GenCountSQL 生成的 sqlStr 存在直接的 group by 时，Session.Count 就会改变原语义了，嗯，其实是应该调整 GenCountSQL 或 genSelectSQL，这里是我一个比较粗暴的简单处理修改，因为并不非常完善也不能完全适用所有情况，所以只是扩展了一个函数，大部分时候倒也是够用了，仅供参考 ``` // Count counts the records. bean's non-empty fields // are conditions. func (session *Session) CountExt(bean ...interface{}) (int64, error) { if session.isAutoClose { defer session.Close() } sqlStr, args, err := session.statement.GenCountSQL(bean...) if err != nil { return 0, err } sqlStrLower := strings.ToLower(sqlStr) groupIndex := strings.LastIndex(sqlStrLower, "group") if groupIndex >= 0 { groupTailStr := sqlStrLower[groupIndex + len("group"):] byIndex := strings.Index(groupTailStr, "by") if byIndex >= 0 && byIndex < 5 { sqlStr = strings.Replace(sqlStr, "count(*)", "1 as t", -1) sqlStr = "select count(*) from (" + sqlStr + ") as t" } } var total int64 err = session.queryRow(sqlStr, args...).Scan(&total) if err == sql.ErrNoRows || err == nil { return total, nil } return 0, err } ```

lunny added the

kind

bug

label 2021-06-06 05:26:52 +00:00

lunny added this to the 1.1.1 milestone 2021-06-06 07:52:23 +00:00

lunny referenced this issue

2021-06-06 07:53:49 +00:00

Fix findandcount or count with groupby #1915

lunny referenced this issue from a commit

2021-06-06 12:21:40 +00:00

Fix findandcount or count with groupby (#1915)

lunny closed this issue