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

在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

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
  • duanjuda5789 2019-01-31 12:33
    关注

    In PostgreSQL

    Query(string sql, args...)

    Here when I am passing values to Query in the form Query(string,values) It is throwing an error that only one value is added, expected should be 2, How should the interface array be passed as args to Query

    I found the answer: you need to pass it in as Query(string, values...)

    评论
  • douao1854 2019-01-31 12:52
    关注

    In orm GORM,we do it like

    if con1 {
        db.Where("con1 =?", con1Flag)
    }
    

    If the orm you write yoursefl, I suggest change into gorm.Or you can refer to the orm you're using ,whether it has the same ussage like code above. If you're coding yourself, do it whatever you like. If you'r working in group, I guess using a mature orm is much better

    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 php5.3内存泄露
  • ¥15 DigSilent如何复制复合模型到自己案例?
  • ¥15 求日版华为b610s-77a 官方公版固件,有偿
  • ¥15 关于#java#的问题,请各位专家解答!(相关搜索:java程序)
  • ¥15 linux tsi721的驱动编译后 insmod 提示 报错
  • ¥20 multisim测数据
  • ¥15 求无向连通网的所有不同构的最小生成树
  • ¥15 模拟器的framebuffer问题
  • ¥15 opencv检测轮廓问题
  • ¥15 单点式登录SSO怎么爬虫获取动态SSO_AUTH_ACCESS_Token