Sqlx join table with same fields-CodePudding

I'm using Go 1.17 with Sqlx but I don't understand how I can join my table correctly.

Here is my structs (my join isn't logic I'm just testing jointure with sqlx)

Table album:

package album

import ".../api/v1/movie"

type Album struct {
    ID      string  `json:"id"`
    Title   string  `json:"title"`
    Artist  string  `json:"artist"`
    Price   float64 `json:"price"`
    MovieId int     `json:"movie_id" db:"movie_id"`
    movie.Movie
}

Table movie:

package movie

type Movie struct {
    ID         string `json:"id"`
    Year       uint16 `json:"year"`
    RentNumber uint32 `json:"rent_number" db:"rent_number"`
    Title      string `json:"title"`
    Author     string `json:"author"`
    Editor     string `json:"editor"`
    Index      string `json:"index"`
    Bib        string `json:"bib"`
    Ref        string `json:"ref"`
    Cat1       string `json:"cat_1" db:"cat_1"`
    Cat2       string `json:"cat_2" db:"cat_2"`
}

And this is how I do my join:

albums := []Album{}
r.db.Select(&albums, "SELECT * FROM album a INNER JOIN movie m ON (m.id=a.movie_id)")

The problem is that these 2 tables have the same id field so the album id is overridden by the movie id and I lost it.

How can I do to ignore the movie id field (because I got it in the movie_id field and keep the field id for the album id ?

CodePudding user response：

You can give one of your id fields an id tag like:

type Album struct {
    ID      string  `json:"id" id:"album_id"`
    Title   string  `json:"title"`
    Artist  string  `json:"artist"`
    Price   float64 `json:"price"`
    movie.Movie
}

and then make a query which aliases the id field to album_id like:

SELECT movie.id as id, album.id as album_id, ... FROM album ...

Just keep in mind that you now need to use this column name in your named queries as well.