how to loop over fields of a struct for query filtering

Question

I have a database where each row corresponds to a struct with the following fields

type item struct {
    ItemId *string `json:"item_id"`
    OwnerId *string `json:"owner_id"`
    Status *string `json:"status"`
    ... // many more
}

Inside the database, all fields are filled for all rows. Now I would like to have a function that takes an item object whose fields may not be filled as input and return a SQL query string. For example

func FindItems(filter item) string

The input item serves as a filter. The logic is as follows (in kind of python style)

query = `select * from item_table`
condition = ""
for field, value in filter:
    if value != nil:
        condition = " and " if condition else " where "
        condition += " field=value"
query += condition

How can I do this in go? Or is there a better way to do filter in go?

Possible duplicate of [Go: Get all fields from an interface](http://stackoverflow.com/questions/39866503/go-get-all-fields-from-an-interface/39866671#39866671). — icza, Dec 04 '16 at 20:08

KAdot · Accepted Answer · 2016-12-05T01:05:50.817

You can use reflect package to enumerate your structure fields and values:

package main

import (
    "fmt"
    "reflect"
)

type item struct {
    ItemID  *string `json:"item_id"`
    OwnerID *string `json:"owner_id"`
    Status  *string `json:"status"`
}

func FindItemsQuery(filter item) string {
    query := `select * from item_table`
    condition := ""
    val := reflect.ValueOf(filter)
    for i := 0; i < val.NumField(); i++ {
        valField := val.Field(i)
        if !valField.IsNil() {
            if condition != "" {
                condition += " and "
            } else {
                condition += " where "
            }
            condition += fmt.Sprintf("%s=%v", val.Type().Field(i).Tag.Get("json"), valField.Elem())
        }
    }
    return query + condition
}

func main() {
    itemID := "123"
    item := item{ItemID: &itemID}
    fmt.Println(FindItemsQuery(item)) // select * from item_table where item_id=123
}

Keep in mind that using additional json tags like json:"item_id,omitempty" will break your query. You should consider using custom structure tags to define the names of SQL fields.

Why do you need `valField.Elem().Interface()` instead of `valField.Elem()`? — nos, Dec 05 '16 at 00:30
@nos I didn't realize `fmt.Sprintf` is able to handle `reflect.Value` type, you are right. — KAdot, Dec 05 '16 at 00:56

how to loop over fields of a struct for query filtering

1 Answers1