Use one struct for multiple SQL queries

Issue

I have multiple similar SQL queries that I need to run in Go code.
Something like this:

type S1 struct {
    X int
    Y int
    Z string
}

rows, err := db.Queryx("SELECT X,Y,Z FROM SomeTable")
for rows.Next() {
    var p S1
    err = rows.StructScan(&p)
}
 
type S2 struct {
    Y int
    Z string
}

rows, err := db.Queryx("SELECT Y,Z FROM SomeTable")
for rows.Next() {
    var p S2
    err = rows.StructScan(&p)
}

type S3 struct {
    X int
    Y int
}

rows, err := db.Queryx("SELECT X,Y FROM SomeTable")
for rows.Next() {
    var p S3
    err = rows.StructScan(&p)
}

Is there a way to use one struct for all 3 queries?
Ideally S1 being used for all 3 queries would be great.
Maybe it is important, Clickhouse database is being used.
SqlX documentation does not imply this is possible, hence the question while I try to figure it out on my own…

Solution

Answer taken from here: https://go.dev/doc/database/querying#multiple_rows

Using the Scan method you can assign populate one variable for every query Column of the query result.

Note: the values not populated by scan(), will default to the zero value of the field type.

type S1 struct {
    X int
    Y int
    Z string
}

func run() {
    rows, err := db.Queryx("SELECT X,Y,Z FROM SomeTable")
    for rows.Next() {
        var p S1
        err = rows.Scan(&S1.X, &S1.Y, &S1.Z)
    }

    rows, err := db.Queryx("SELECT Y,Z FROM SomeTable")
    for rows.Next() {
        var p S1
        err = rows.Scan(&S1.Y, &S1.Z)
    }

    rows, err := db.Queryx("SELECT X,Y FROM SomeTable")
    for rows.Next() {
        var p S1
        err = rows.Scan(&S1.X, &S1.Y)
    }
}

Answered By – Andres Bott

Answer Checked By – Marilyn (GoLangFix Volunteer)

Leave a Reply

Your email address will not be published.