go-pg如何从相关表中选择实体？

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

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

问题

  1. 类型 Book 结构体 {
  2. 	tableName struct{} `pg:"book" json:"-"`
  3. 	Id        int      `pg:"id,pk" json:"id"`
  4. 	Author    int      `pg:"author_id,notnull" json:"-"`
  5. 	Author    *Author  `pg:"fk:author_id" json:"author,omitempty"`
  6. }

我想在一次查询中选择书籍和作者。

如果我尝试这样做：

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

我会得到一个错误

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

  2. <details>
  3. <summary>英文:</summary>

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

  2. I want select book and author in one query.
  4. 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)

  1. I get an error 
  3. &gt; pg: can&#39;t find column=name in model=Book (try discard_unknown_columns)
  6. </details>
  9. # 答案1
  10. **得分**: 1
  12. 我写了一段代码，每次处理这种情况时都会用到。首先，让我展示代码，然后我会对相关部分进行评论：
  14. ```go
  15. package main
  17. import (
  18. 	"database/sql"
  19. 	"fmt"
  21. 	_ "github.com/lib/pq"
  22. )
  24. type Book struct {
  25. 	gorm.Model
  26. 	Title       string
  27. 	Description string
  28. 	AuthorID    uint
  29. 	Author      Author
  30. }
  32. type Author struct {
  33. 	gorm.Model
  34. 	FirstName string
  35. 	LastName  string
  36. 	Books     []Book
  37. }
  39. type Result struct {
  40. 	BookId    int
  41. 	AuthorId  int
  42. 	Title     string
  43. 	FirstName string
  44. 	LastName  string
  45. }
  47. func main() {
  48. 	conn, err := sql.Open("postgres", "host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable")
  49. 	if err != nil {
  50. 		panic(err)
  51. 	}
  52. 	defer conn.Close()
  53. 	// 查询
  54. 	var result []Result
  55. 	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")
  56. 	if err != nil {
  57. 		panic(err)
  58. 	}
  59. 	defer rows.Close()
  60. 	for rows.Next() {
  61. 		var record Result
  62. 		if err := rows.Scan(&record.BookId, &record.AuthorId, &record.Title, &record.FirstName, &record.LastName); err != nil {
  63. 			panic(err)
  64. 		}
  65. 		result = append(result, record)
  66. 	}
  68. 	if err := rows.Err(); err != nil {
  69. 		panic(err)
  70. 	}
  72. 	fmt.Printf("%v", result)
  73. }

结构体定义

Book 和 Author 结构体表示我数据库中定义的表。Result 用于保存通过下面指定的查询获取的记录。

查询

查询非常简单。我们只在 main 函数开始时使用 SQL 客户端的 Query 方法。然后，我们需要延迟调用 rows 变量的 Close 方法进行清理。

扫描

for 循环确保我们扫描了 Query 方法检索到的所有行。为了判断是否还有其他行需要获取，我们使用 Next 方法，它返回一个布尔值，指示是否还有其他行需要扫描。
在 for 循环的主体中，我们声明一个循环作用域变量来保存当前记录。通过 Scan 方法，我们将能够将每个列分配给结构体的相应字段。
最后，我们需要通过在 rows 变量上调用 Err 方法来检查是否有任何错误，并进行处理。

如果这解答了你的问题，请告诉我，谢谢！

英文:

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:

  1. package main
  3. import (
  4. 	&quot;database/sql&quot;
  5. 	&quot;fmt&quot;
  7. 	_ &quot;github.com/lib/pq&quot;
  8. )
  10. type Book struct {
  11. 	gorm.Model
  12. 	Title       string
  13. 	Description string
  14. 	AuthorID    uint
  15. 	Author      Author
  16. }
  18. type Author struct {
  19. 	gorm.Model
  20. 	FirstName string
  21. 	LastName  string
  22. 	Books     []Book
  23. }
  25. type Result struct {
  26. 	BookId    int
  27. 	AuthorId  int
  28. 	Title     string
  29. 	FirstName string
  30. 	LastName  string
  31. }
  33. func main() {
  34. 	conn, err := sql.Open(&quot;postgres&quot;, &quot;host=localhost user=postgres password=postgres dbname=postgres port=5432 sslmode=disable&quot;)
  35. 	if err != nil {
  36. 		panic(err)
  37. 	}
  38. 	defer conn.Close()
  39. 	// query
  40. 	var result []Result
  41. 	rows, err := conn.Query(&quot;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&quot;)
  42. 	if err != nil {
  43. 		panic(err)
  44. 	}
  45. 	defer rows.Close()
  46. 	for rows.Next() {
  47. 		var record Result
  48. 		if err := rows.Scan(&amp;record.BookId, &amp;record.AuthorId, &amp;record.Title, &amp;record.FirstName, &amp;record.LastName); err != nil {
  49. 			panic(err)
  50. 		}
  51. 		result = append(result, record)
  52. 	}
  54. 	if err := rows.Err(); err != nil {
  55. 		panic(err)
  56. 	}
  58. 	fmt.Printf(&quot;%v&quot;, result)
  59. }

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!

huangapple
  • 本文由 发表于 2022年12月18日 19:41:12
  • 转载请务必保留本文链接：https://go.coder-hub.com/74841011.html
  • go
  • go-pg
匿名

发表评论

匿名网友

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

确定