Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
How to bind to struct with join query in sqlx
package main
import (
"fmt"
_ "github.com/go-sql-driver/mysql"
"github.com/jmoiron/sqlx"
)
type Hoge struct {
ID int `db:"id"`
Foo string `db:"foo"`
}
type Fuga struct {
ID int `db:"id"`
Hoge Hoge `db:"hoge"`
Bar string `db:"bar"`
}
func main() {
db, err := connect()
if err != nil {
panic(err)
}
s := "SELECT h.id as \"hoge.id\", h.foo AS \"hoge.foo\", f.id as id, f.bar as bar FROM fuga AS f JOIN hoge AS h ON f.hoge_id = h.id"
var hs []Fuga
err = db.Select(&hs, s)
if err != nil {
panic(err)
}
fmt.Printf("%+v\n", hs)
// => [{ID:1 Hoge:{ID:1 Foo:aaa} Bar:xxx} {ID:2 Hoge:{ID:1 Foo:aaa} Bar:yyy} {ID:3 Hoge:{ID:2 Foo:bbb} Bar:zzz}]
}
func connect() (*sqlx.DB, error) {
return sqlx.Connect(
"mysql",
fmt.Sprintf(
"%s:%s@tcp(%s:%s)/%s",
"root",
"",
"localhost",
"3306",
"test",
),
)
}
CREATE TABLE `hoge` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`foo` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `fuga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hoge_id` int(11) NOT NULL,
`bar` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO hoge (foo) VALUES ("aaa"), ("bbb"), ("ccc");
INSERT INTO fuga (hoge_id, bar) VALUES (1, "xxx"), (1, "yyy"), (2, "zzz");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment