如何在 PostgreSQL 行内读取/写入对象数组?(GoLang)

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

How can I read/write an array of objects inside a PostgreSQL row? (GoLang)

问题

我在Go语言中定义了以下类型:

type Comment struct {
	Id      int
	User    string
	Email   string
	Date    string
	Comment string
}

type Post struct {
	Id       int
	Special  bool
	Title    string
	Date     string
	Content  string
	Image    string
	Comments []Comment
}

我需要知道如何修改这段代码:

OpenDB()
rows, _ := cn.Query(`SELECT id, date, title, special, content, image
			FROM posts ORDER BY date DESC LIMIT $1
			OFFSET $2`, fmt.Sprint(limit), fmt.Sprint(offset))
posts := []Post{}
for rows.Next() {
  post := Post{}
  e := rows.Scan(&post.Id, &post.Date, &post.Title,
				&post.Special, &post.Content, &post.Image)
  if e != nil {
	panic(e)
  }
  posts = append(posts, post)
}

以允许读取评论。另外,我该如何修改以下代码:

OpenDB()
_, e = cn.Exec(`INSERT INTO
				posts(date, title, special, content, image)
				VALUES ($1, $2, $3, $4, $5)`, date, title, special, content, image)
if e != nil {
	panic(e)
}
defer CloseDB()

以允许写入一个空的评论数组。

最后,如果有人告诉我如何将单个评论写入现有的帖子,我将非常感激。

英文:

I defined this types in GoLang:

type Comment struct {
	Id          int
	User        string
	Email       string
	Date        string
	Comment     string
}

type Post struct {
	Id          int
	Special     bool
	Title       string
	Date        string
	Content     string
	Image       string
	Comments    []Comment
}

I need to know how to modify this code:

OpenDB()
rows, _ := cn.Query(`SELECT id, date, title, special, content, image
			FROM posts ORDER BY date DESC LIMIT $1
			OFFSET $2`, fmt.Sprint(limit), fmt.Sprint(offset))
posts := []Post{}
for rows.Next() {
  post := Post{}
  e := rows.Scan(&post.Id, &post.Date, &post.Title,
				&post.Special, &post.Content, &post.Image)
  if e != nil {
	panic(e)
  }
  posts = append(posts, post)
}

To allow reading comments. And also, how I can modify:

OpenDB()
_, e = cn.Exec(`INSERT INTO
				posts(date, title, special, content, image)
				VALUES ($1, $2, $3, $4, $5)`, date, title, special, content, image)
if e != nil {
	panic(e)
}
defer CloseDB()

To allow writting an empty array of comments.

Finally I would be grateful if someone tell me how can I write single comments into an existing post.

答案1

得分: 0

这取决于你的数据库架构。


如果Comment有自己的表,你需要在插入Post之后循环遍历所有Post中的评论,并将它们插入。cn.Exec应该返回一个Result,可以用它来获取最后插入的ID,例如:

result, err := cn.Exec(...)
if err != nil {
  panic(err)
}

id, err := result.LastInsertId()
if err != nil {
  panic(err)
}

现在你可以将你的帖子ID作为外键使用。


如果你使用一个JSON列来存储你的评论,你应该定义一个自定义类型作为[]Comment的别名,并使该类型实现sql.Scannerdriver.Valuer接口。

type Comment struct {
    Id          int
    User        string
    Email       string
    Date        string
    Comment     string
}

type Comments []Comment

// 让Comments类型实现driver.Valuer接口。该方法简单地返回结构体的JSON编码表示。
func (c Comments) Value() (driver.Value, error) {
    return json.Marshal(c)
}

// 让Comments类型实现sql.Scanner接口。该方法简单地将JSON编码的值解码为结构体字段。
func (c *Comments) Scan(value interface{}) error {
    var b []byte
    switch t := value.(type) {
    case []byte:
        b = t
    case string:
        b = []byte(t)
    default:
        return errors.New("未知类型")
    }

    return json.Unmarshal(b, &c)
}
英文:

This depends on your database schema.


If Comment has its own table you will have to loop over all comments in a Post and insert them after you have have inserted the Post. cn.Exec should return a Result which can be used to get the last inserted ID like:

result, err := cn.Exec(...)
if err != nil {
  panic(err)
}

id, err := result.LastInsertId()
if err != nil {
  panic(err)
}

You can now use the ID of your post as a foreign key.


If you are using a JSON column to store your comments you should define a custom type as alias for []Comment and make the type implement sql.Scanner and driver.Valuer.

type Comment struct {
    Id          int
    User        string
    Email       string
    Date        string
    Comment     string
}

type Comments []Comment

// Make the Comments type implement the driver.Valuer interface. This method
// simply returns the JSON-encoded representation of the struct.
func (c Comments) Value() (driver.Value, error) {
    return json.Marshal(c)
}

// Make the Comments type implement the sql.Scanner interface. This method
// simply decodes a JSON-encoded value into the struct fields.
func (c *Comments) Scan(value interface{}) error {
    var b []byte
    switch t := value.(type) {
    case []byte:
        b = t
    case string:
        b = string(t)
    default:
        return errors.New("unknown type")
    }

    return json.Unmarshal(b, &c)
}

huangapple
  • 本文由 发表于 2021年11月14日 03:50:17
  • 转载请务必保留本文链接:https://go.coder-hub.com/69957715.html
匿名

发表评论

匿名网友

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

确定