Sqlx join table with same fields

huangapple go评论189阅读模式
英文:

Sqlx join table with same fields

问题

我正在使用Go 1.17和Sqlx,但我不明白如何正确地连接我的表。

这是我的结构体(我的连接不是逻辑正确的,我只是用sqlx测试连接):

表 album:

  1. package album
  2. import ".../api/v1/movie"
  3. type Album struct {
  4. ID string `json:"id"`
  5. Title string `json:"title"`
  6. Artist string `json:"artist"`
  7. Price float64 `json:"price"`
  8. MovieId int `json:"movie_id" db:"movie_id"`
  9. movie.Movie
  10. }

表 movie:

  1. package movie
  2. type Movie struct {
  3. ID string `json:"id"`
  4. Year uint16 `json:"year"`
  5. RentNumber uint32 `json:"rent_number" db:"rent_number"`
  6. Title string `json:"title"`
  7. Author string `json:"author"`
  8. Editor string `json:"editor"`
  9. Index string `json:"index"`
  10. Bib string `json:"bib"`
  11. Ref string `json:"ref"`
  12. Cat1 string `json:"cat_1" db:"cat_1"`
  13. Cat2 string `json:"cat_2" db:"cat_2"`
  14. }

这是我进行连接的方式:

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

问题是这两个表都有相同的id字段,所以专辑的id被电影的id覆盖了,我丢失了它。

我该如何忽略电影的id字段(因为我在movie_id字段中已经有了它),并保留专辑的id字段?

英文:

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:

  1. package album
  2. import ".../api/v1/movie"
  3. type Album struct {
  4. ID string `json:"id"`
  5. Title string `json:"title"`
  6. Artist string `json:"artist"`
  7. Price float64 `json:"price"`
  8. MovieId int `json:"movie_id" db:"movie_id"`
  9. movie.Movie
  10. }

Table movie:

  1. package movie
  2. type Movie struct {
  3. ID string `json:"id"`
  4. Year uint16 `json:"year"`
  5. RentNumber uint32 `json:"rent_number" db:"rent_number"`
  6. Title string `json:"title"`
  7. Author string `json:"author"`
  8. Editor string `json:"editor"`
  9. Index string `json:"index"`
  10. Bib string `json:"bib"`
  11. Ref string `json:"ref"`
  12. Cat1 string `json:"cat_1" db:"cat_1"`
  13. Cat2 string `json:"cat_2" db:"cat_2"`
  14. }

And this is how I do my join:

  1. albums := []Album{}
  2. 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 ?

答案1

得分: 1

你可以给你的一个id字段添加一个id标签,例如:

  1. type Album struct {
  2. ID string `json:"id" id:"album_id"`
  3. Title string `json:"title"`
  4. Artist string `json:"artist"`
  5. Price float64 `json:"price"`
  6. movie.Movie
  7. }

然后,可以创建一个查询,将id字段别名为album_id,例如:

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

请记住,你现在需要在你的命名查询中使用这个列名。

英文:

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

  1. type Album struct {
  2. ID string `json:"id" id:"album_id"`
  3. Title string `json:"title"`
  4. Artist string `json:"artist"`
  5. Price float64 `json:"price"`
  6. movie.Movie
  7. }

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

  1. 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.

huangapple
  • 本文由 发表于 2021年11月16日 01:31:42
  • 转载请务必保留本文链接:https://go.coder-hub.com/69978502.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定