如何构建一个select子查询中函数带复杂条件的SQL

[wnkw]

想要构建一个select子查询中函数带复杂条件SQL，例如clickhouse的countIf

举例一个目标SQL：

select
    countIf (
        (
            columnA in (1, 2)
            OR (columnB = 'hello')
        )
        AND columnC = 3
    ) as c1
from
    table_XXX

然而我发现下面这种简单的方式并没有达到预期：

func CountIf(tbl *gorm.DB) *gorm.DB {
    term := tbl
    subTerm := term.Where(term.Where("columnA in (?)", []uint32{1, 2}).Or("columnB = ?", "hello")).
        Where("columnC = ?", 3)
    return tbl.Select("countIf(?) as c1", subTerm)
}

// SQL output:  SELECT countIf(SELECT * FROM `fake` WHERE (columnA in (1,2) OR columnB = 'hello') AND columnC = 3) as c1 FROM `table_XXX`

又或者

func CountIf(tbl *gorm.DB) *gorm.DB {
    term := tbl
    subTerm := term.Where(term.Where("columnA in (?)", []uint32{1, 2}).Or("columnB = ?", "hello")).
        Where("columnC = ?", 3)
    subTerm.Statement.Build("WHERE")
    return tbl.Select("countIf(?) as c1", subTerm)
}

// SQL output: SELECT countIf(WHERE (columnA in (1,2) OR columnB = 'hello') AND columnC = 3) as c1 FROM `table_XXX`

我自己当前的实现

func CountIf(tbl *gorm.DB) *gorm.DB {
    term := tbl
    subTerm := term.Where(term.Where("columnA in (?)", []uint32{1, 2}).Or("columnB = ?", "hello")).
        Where("columnC = ?", 3)

    return tbl.Select("countIf(?) as c1", GetWhereCond(subTerm))
}

func GetWhereCond(term *gorm.DB) *gorm.DB {
    if term.Statement == nil || term.Statement.Clauses == nil {
        return term
    }
    whereClause := term.Statement.Clauses["WHERE"]
    whereClause.Name = ""
    term.Statement.Clauses[" "] = whereClause
    term.Statement.Build(" ")
    return term
}

// SQL output: SELECT countIf((columnA in (1,2) OR columnB = 'hello') AND columnC = 3) as c1 FROM `table_XXX`

想请教下有没有更好的实现方法？

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days