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

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.

CodePudding user response：

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)
}