I'm building a simple Go REST API with MySQL and I'm having some trouble querying the data when it comes to slightly more complicated queries. I have 2 models: books and genres. The model definitions in go are as below:
type Book struct {
ID string `json:"id"`
Title string `json:"title"`
Genre *Genre `json:"genre"`
Price float64 `json:"price"`
}
type Genre struct {
ID string `json:"id"`
Name string `json:"name"`
}
In SQL, the book table has the fields id, title, price, and id_genre which is a foreign key.
The confusion is after performing the select which returns the rows, I'm not sure how to add the information to the Genre field when I'm looping through the rows
result, err := database.DB.Query("SELECT a.id_book, a.title, a.price, b.name, b.id_genre FROM bookshelf.books a JOIN bookshelf.genres b ON a.id_genre = b.id_genre")
for result.Next() {
var book models.Book
// ????
err := result.Scan(&book.ID, &book.Title, &book.Price, &book.Genre )
if err != nil {
panic(err.Error())
}
// ...more code
}
Clearly in the result.Scan method is wrong, but I'm not sure how to go about the genre part.
Is this the right approach or am I approaching this all wrong?