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.

icza
– icza

2016-12-04 20:08:09 +00:00
Commented Dec 4, 2016 at 20:08 — icza
– icza, Commented Dec 4, 2016 at 20:08

KAdot · Accepted Answer · 2016-12-05 01:05:50Z

2

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.

edited Dec 5, 2016 at 1:05

answered Dec 4, 2016 at 18:13

KAdot

2,09714 silver badges22 bronze badges

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

2 Comments

nos Over a year ago

Why do you need valField.Elem().Interface() instead of valField.Elem()?

KAdot Over a year ago

@nos I didn't realize fmt.Sprintf is able to handle reflect.Value type, you are right.

Collectives™ on Stack Overflow

how to loop over fields of a struct for query filtering

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related