go-pg how to select also entity from related table?

Question

type Book struct {
    tableName struct{} `pg:"book" json:"-"`
    Id        int      `pg:"id,pk" json:"id"`
    Author    int      `pg:"author_id,notnull" json:"-"`
    Author    *Author  `pg:"fk:author_id" json:"author,omitempty"`
}

I want select book and author in one query.

If I try this:

var r []model.Book
_, err := dao.FusedDb.Query(&r, `SELECT * FROM book b INNER JOIN author a on a.id = b.author_id`)

I get an error

pg: can't find column=name in model=Book (try discard_unknown_columns)

Answer 1

I wrote down a piece of code that I always use when I've to deal with this scenario. First, let me show the code and then I'll comment on the relevant parts:

package main

import (
    "database/sql"
    "fmt"

    _ "github.com/lib/pq"
)

type Book struct {
    gorm.Model
    Title       string
    Description string
    AuthorID    uint
    Author      Author
}

type Author struct {
    gorm.Model
    FirstName string
    LastName  string
    Books     []Book
}

type Result struct {
    BookId    int
    AuthorId  int
    Title     string
    FirstName string
    LastName  string
}

func main() {
    conn, err := sql.Open("postgres", "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer conn.Close()
    // query
    var result []Result
    rows, err := conn.Query("select b.id, a.id, b.title, a.first_name, a.last_name from authors a inner join books b on a.id = b.author_id")
    if err != nil {
        panic(err)
    }
    defer rows.Close()
    for rows.Next() {
        var record Result
        if err := rows.Scan(&record.BookId, &record.AuthorId, &record.Title, &record.FirstName, &record.LastName); err != nil {
            panic(err)
        }
        result = append(result, record)
    }

    if err := rows.Err(); err != nil {
        panic(err)
    }

    fmt.Printf("%v", result)
}

Structs definition

The Book and Author structs represent the tables defined in my database. Result is used to hold the fetched records through the query specified below.

The query

The query is pretty straightforward. We only used the method Query on the SQL client opened at the beginning of the main function. Then, we've to defer a call to the method Close on the rows variable to clean up.

Scanning

The for makes sure that we scan all of the rows retrieved with the Query method. To understand if there are other rows to fetch we use the method Next that returns a bool value indicating whether or not there are other rows to scan.
In the body of the for we declare a loop-scoped variable to hold the current record. Thanks to the Scan method we'll be able to assign each column to the relative field of the struct.
Lastly, we've to check for any error by invoking the method Err on the rows variable and handle it.
Let me know if this clarifies your question, thanks!