关于SegmentFault:聊聊gorm的GroupBy

序

本文次要钻研一下 gorm 的 GroupBy

GroupBy

gorm.io/gorm@v1.20.11/clause/group_by.go

// GroupBy group by clause
type GroupBy struct {Columns []Column
    Having  []Expression}

// Name from clause name
func (groupBy GroupBy) Name() string {return "GROUP BY"}

// Build build group by clause
func (groupBy GroupBy) Build(builder Builder) {
    for idx, column := range groupBy.Columns {
        if idx > 0 {builder.WriteByte(',')
        }

        builder.WriteQuoted(column)
    }

    if len(groupBy.Having) > 0 {builder.WriteString("HAVING")
        Where{Exprs: groupBy.Having}.Build(builder)
    }
}

// MergeClause merge group by clause
func (groupBy GroupBy) MergeClause(clause *Clause) {if v, ok := clause.Expression.(GroupBy); ok {copiedColumns := make([]Column, len(v.Columns))
        copy(copiedColumns, v.Columns)
        groupBy.Columns = append(copiedColumns, groupBy.Columns...)

        copiedHaving := make([]Expression, len(v.Having))
        copy(copiedHaving, v.Having)
        groupBy.Having = append(copiedHaving, groupBy.Having...)
    }
    clause.Expression = groupBy
}

GroupBy 定义了 Columns 和 Having 属性，其 Build 办法遍历 Columns，最初针对 Having 在拼接 Having 子句

实例

func TestGroupBy(t *testing.T) {results := []struct {Clauses []clause.Interface
        Result  string
        Vars    []interface{}
    }{
        {[]clause.Interface{clause.Select{}, clause.From{}, clause.GroupBy{Columns: []clause.Column{{Name: "role"}},
                Having:  []clause.Expression{clause.Eq{"role", "admin"}},
            }},
            "SELECT * FROM `users` GROUP BY `role` HAVING `role` = ?", []interface{}{"admin"},
        },
        {[]clause.Interface{clause.Select{}, clause.From{}, clause.GroupBy{Columns: []clause.Column{{Name: "role"}},
                Having:  []clause.Expression{clause.Eq{"role", "admin"}},
            }, clause.GroupBy{Columns: []clause.Column{{Name: "gender"}},
                Having:  []clause.Expression{clause.Neq{"gender", "U"}},
            }},
            "SELECT * FROM `users` GROUP BY `role`,`gender` HAVING `role` = ? AND `gender` <> ?", []interface{}{"admin", "U"},
        },
    }

    for idx, result := range results {t.Run(fmt.Sprintf("case #%v", idx), func(t *testing.T) {checkBuildClauses(t, result.Clauses, result.Result, result.Vars)
        })
    }
}

小结

gorm 的 GroupBy 定义了 Columns 和 Having 属性，其 Build 办法遍历 Columns，最初针对 Having 在拼接 Having 子句。

doc

• gorm