英文:
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.Scanner和driver.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)
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论