dongyuling0312 2018-09-14 08:15
浏览 1327
已采纳

在Golang中建立动态(条件)WHERE SQL查询

I'm using golang, go_reform, PostgreSQL. What i want to do is a REST search utility, and all went fine until I faced with conditional search query. "Conditional" here means I have 10 columns in a table to search in, and there may be ton of combinations so I can't handle them all separately. What i need is a query builder, but I have no understanding how can I implement this in Go. For now I have an idea like this, but it seems not really efficient

type Query struct {
    Id               *int64
    FirstName        *string
    MiddleName       *string
    LastName         *string
    AreaId           *int64
    Birthday         *time.Time
}

func (table *Query) Find() (*User) {
    if table.Id != nil {
        idstr := fmt.Sprintf("WHERE Id = %d AND ", table.Id)
    }
    else idstr := "WHERE "
    }
    if table.FirstName != "" {
        firststr := fmt.Sprintf("FirstName = %s AND", table.FirstName)
    }
    else firststr := ""
}//and so on

That feels really awkward so I'm wondering is there any better way to determine the fields that came to the Find() and build a SQL query based on this. (Actually it's coming in JSON and binding to Query struct, so maybe there is a way without struct). There also may be SQL workarounds, but I think it would be more efficient to build query without all possible columns.

EDIT: By the way, making my Google search query more accurate, i found a bunch of things related to my problem, probably i will try to use it now. For those who interested too: old go playground example

Making dynamic SQL queries to a MySQL DB

gorp package (snippets thing sounds very promising)

  • 写回答

3条回答 默认 最新

  • dtpd58676 2018-09-14 11:03
    关注

    So, i found the solution. Big thanks to this guy, his code fits perfectly for me, love the way it works. Shame on guys who think that if-else boilerplate is the only way to make this possible. My exact code is this: Controller

    func Find(c echo.Context) (err error) {
    model := &models.Query{}
    if err = c.Bind(model); err != nil {
        return c.JSON(http.StatusInternalServerError, u.Message(false, "Bad request"))
    }
    resp := model
    

    .Find() return c.JSON(http.StatusOK, resp) Model function:

    type Query map[string]interface{}
    
    func (model Query) Find() (Query) {
        var values []interface{}
        var where []string
        for k, v := range model {
            values = append(values, v)
            //MySQL Way: where = append(where, fmt.Sprintf("%s = ?", k))
            where = append(where, fmt.Sprintf(`"%s" = %s`,k, "$" + strconv.Itoa(len(values))))
        }
        string := ("SELECT name FROM users WHERE " + strings.Join(where, " AND "))
        //for testing purposes i didn't ran actual query, just print it in the console and returned JSON back
        fmt.Println(string)
        return model
    
    }
    

    UPD: For PostgreSQL users like me (thanks to @mkopriva and his playground example), I'm able to have this placeholder thing working right on PostgreSQL

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 SPSS分类模型实训题步骤
  • ¥15 求解决扩散模型代码问题
  • ¥15 工创大赛太阳能电动车项目零基础要学什么
  • ¥20 limma多组间分析最终p值只有一个
  • ¥15 nopCommerce开发问题
  • ¥15 torch.multiprocessing.spawn.ProcessExitedException: process 1 terminated with signal SIGKILL
  • ¥15 QuartusⅡ15.0编译项目后,output_files中的.jdi、.sld、.sof不更新怎么解决
  • ¥15 pycharm输出和导师的一样,但是标红
  • ¥15 想问问富文本拿到的html怎么转成docx的
  • ¥15 我看了您的文章,遇到了个问题。