Build dynamic (conditional) WHERE SQL query in Golang

Question

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)

Aside from the compilation errors (i.e. elector.FirstName is a pointer, so cannot be compared to a string), it's a reasonable approach. — Jonathan Hall
– Jonathan Hall, Commented Sep 14, 2018 at 8:18
Do not use fmt verbs (the %d, %s etc. in your strings) to construct your queries, you are setting yourself up for some sql injection. — mkopriva
– mkopriva, Commented Sep 14, 2018 at 8:36
My opinion about ORMs is that they make already easy tasks slightly easier, and complex tasks impossible. I think they are evil, and should always be avoided, no exceptions. My opinion isn't always popular, but that's my opinion. — Jonathan Hall
– Jonathan Hall, Commented Sep 14, 2018 at 9:27
@PavelNasevich If you don't mind using reflect you can do something like this: play.golang.org/p/BlADhht9PwO — mkopriva
– mkopriva, Commented Sep 14, 2018 at 9:48
@EliasVanOotegem: An ORM, per se, is not what's needed. A query builder is. ORMs often include these as a feature, but there's no reason you need an ORM to get that feature. — Jonathan Hall
– Jonathan Hall, Commented Sep 14, 2018 at 10:44

Benjamin W. · Accepted Answer · 2020-04-08 19:35:56Z

16

So, I found the solution. Big thanks to Cerise Limón, whose code fits perfectly for me.

The solution I ended up with

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

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

}

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

edited Apr 8, 2020 at 19:35

Benjamin W.

54k19 gold badges135 silver badges136 bronze badges

answered Sep 14, 2018 at 11:03

Pavel Nasevich

4321 gold badge4 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Pavel Nasevich Over a year ago

Basically, i still have a one little problem - i use PostgreSQL, so placeholder "?" is a no-go here, and i don't have an idea how to make it replace it to $1, $2 and so on.

hobberwickey Over a year ago

This can leave you open to SQL injection no? Where are you sanitizing your values?

frost Over a year ago

This is not susceptible to SQL injection because placeholders are used for parts of the query outside the application's direct control.

fwhez · Accepted Answer · 2019-01-31 12:52:48Z

2

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

answered Jan 31, 2019 at 12:52

fwhez

5814 silver badges11 bronze badges

Comments

Rakesh · Accepted Answer · 2019-01-31 14:56:17Z

0

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...)

edited Jan 31, 2019 at 14:56

Rakesh

3,3992 gold badges25 silver badges42 bronze badges

answered Jan 31, 2019 at 12:33

Parwinder Kumar

811 silver badge10 bronze badges

Collectives™ on Stack Overflow

Build dynamic (conditional) WHERE SQL query in Golang

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related