Go language, scanning embeded struct with sqlx.Str

Question

I have just started learning Go language. I wrote following simple program.

Here i am trying to fill struct with all Books and related Authers.

Book struct has embeded Author struct.

package main
import (
    "fmt"
    "log"
    "time"
    "github.com/jmoiron/sqlx"
    _ "github.com/lib/pq"
)

type Book struct {
    ID      int
    Title   string
    Year    int
    Bauther  Auther `db:"auther"`
}

type Auther struct {
    ID      int
    Name    string
    Dob     time.Time
}

func main() {
    db, err := sqlx.Open("postgres", "host=localhost user=testuser dbname=testdb password=testuser")
    if err != nil {
       log.Fatal("DB Conn error: ", err)
    }

    if err = db.Ping(); err != nil {
        log.Fatal("DB Ping error: ", err)
    }
    defer db.Close()

    rows, err := db.Queryx("Select b.*, a.name from books b left outer join authers a on a.ID=b.auther;")
    if err != nil {
        log.Fatal("DB Query error: ", err)
    }
    defer rows.Close()

    var books []*Book
    for rows.Next() {
        var b = &Book{}
        err := rows.StructScan(b)
        if err != nil {
            log.Fatal("Scan error: ", err)
        }
        books = append(books, b)
    }

    // print all books
    for _, b := range books {
        fmt.Printf("%v", b)
    }
}

But when i run it, it is giving following error

[samtech@sam sqlxapp]$ go run main.go
2016/02/11 18:45:46 Scan error: missing destination name name
exit status 1

What i am doing wrong?

I have also tried changing field tag in Book struct to

Bauther  Auther `db:"auther,prefix=auth."`

and change query to

rows, err := db.Queryx("Select b.*, auth.name from books b left outer join authers auth on auth.ID=b.auther;")

But it doesn't make any change.

EDIT

After few try and errors, finally i make it working.

I have to slightly change the Models that i have created. I changed Book struct from

type Book struct {
    ID      int
    Title   string
    Year    int
    Bauther Auther
}

to

type Book struct {
    ID          int                  // Key
    Title       string
    Year        int
    AutherID    int `db:"auther"`   // FKey
    Auther 
}

Now, it is working fine. The mistake i was doing was, i added Bauther field as Auther. Sqlx could not understand it. But when i added Auther as anonymous embedded struct, the problem resolved.

But it introduces another problem :)

As ID field exist in Book as well as in Auther both the structs. Now ScanStruct is filling Book.ID with 0 in all rows.

Is there anything that i can do to avoid it?

Answer 1

This doesn't seem possible with sqlx at the moment. There is a open issue for this: https://github.com/jmoiron/sqlx/issues/131

You can however do that easily without using sqlx:

package main

import (
    "database/sql"
    "fmt"
    "log"
    "time"

    _ "github.com/lib/pq"
)

type Book struct {
    ID      int
    Title   string
    Year    int
    Bauther Auther
}

type Auther struct {
    ID   int
    Name string
    Dob  time.Time
}

func main() {
    db, err := sql.Open("postgres", "postgres://localhost/testdb?sslmode=disable")
    if err != nil {
        log.Fatal("DB Conn error: ", err)
    }

    if err = db.Ping(); err != nil {
        log.Fatal("DB Ping error: ", err)
    }
    defer db.Close()

    rows, err := db.Query("Select b.id, b.title, b.year, a.id, a.name, a.dob from books b left outer join authers a on a.ID=b.auther;")
    if err != nil {
        log.Fatal("DB Query error: ", err)
    }
    defer rows.Close()

    var books []*Book
    for rows.Next() {
        var b = &Book{}
        if err := rows.Scan(&b.ID, &b.Title, &b.Year, &b.Bauther.ID, &b.Bauther.Name, &b.Bauther.Dob); err != nil {
            log.Fatal(err)
        }
        books = append(books, b)
    }

    // print all books
    for _, b := range books {
        fmt.Printf("%v", b)
    }
}

Basically you have to be explicit of your columns in your SELECT statement, so that you know the order of them. Then you create your struct and scan into each element with the same order as your columns in your SELECT statement.