Bdtjtk

I'm trying to create a basic commenting api in go. I can't seem to figure out how to scan postgresql arrays into an array of structs within a struct. I think I could probably have Thread.Posts type be jsonb but that seems inelegant since I would have to unmarshall it I think.

sql: Scan error on column index 3, name "posts": unsupported Scan,
storing driver.Value type uint8 into type *models.Post

var threadSchema = `

CREATE TABLE IF NOT EXISTS thread (

  id         SERIAL PRIMARY KEY,

  name       VARCHAR(100) NOT NULL,

  profile_id INTEGER REFERENCES profile (id)

)`



var postSchema = `

CREATE TABLE IF NOT EXISTS post (

  id         SERIAL PRIMARY KEY,

  comment    TEXT,

  profile_id INTEGER REFERENCES profile (id),

  thread_id  INTEGER REFERENCES thread (id)

)`



type Post struct {

    Id        int    `db:"id" json:"id"`

    Comment   string `db:"comment" json:"comment" binding:"required" form:"comment"`

    ProfileId int   `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`

    ThreadId  int    `db:"thread_id" json:"thread_id" binding:"required" form:"thread_id"`

}



type Thread struct {

    Id        int    `db:"id" json:"id"`

    Name      string `db:"name" json:"name" binding:"required" form:"name"`

    ProfileId int    `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`

    Posts     Post `db:"posts" json:"posts" form:"posts"`

}



func GetThreads(db *sqlx.DB, c *gin.Context) {

    threads := Thread{}

    err := db.Select(&threads, `

    SELECT thread.id,thread.name,thread.profile_id,array_agg(post.id) AS posts

    FROM thread

    INNER JOIN post ON thread.id = post.thread_id

    GROUP BY thread.id;

  `)

    if err != nil {

        log.Fatal(err)

    }

    c.JSON(http.StatusOK, gin.H{"data": threads})

}

First off, you can't do this with sqlx, whether or not you're using Postgres arrays.

Second, your SQL query is simply aggregating Post IDs, not the content of the posts, so there's no way to get the data you want (using Go or otherwise).

So here's what you can do:

1. 
   

   Use an anonymous embedded struct, capture all of the Post content in your SQL query, and then merge your duplicated Threads.

   
   
   

   type Post struct {
   
       Id        int    `db:"id" json:"id"`
   
       Comment   string `db:"comment" json:"comment" binding:"required" form:"comment"`
   
       ProfileId int   `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
   
       ThreadId  int    `db:"thread_id" json:"thread_id" binding:"required" form:"thread_id"`
   
   }
   
   
   
   type ThreadDb struct {
   
       Id        int    `db:"id" json:"id"`
   
       Name      string `db:"name" json:"name" binding:"required" form:"name"`
   
       ProfileId int    `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
   
       Post
   
   }
   
   
   
   type Thread struct {
   
       Id        int    `db:"id" json:"id"`
   
       Name      string `db:"name" json:"name" binding:"required" form:"name"`
   
       ProfileId int    `db:"profile_id" json:"profile_id" binding:"required" form:"profile_id"`
   
       Posts     Post `db:"posts" json:"posts" form:"posts"`
   
   }
   
   
   
   
   
   func GetThreads(db *sqlx.DB, c *gin.Context) {
   
       threads := ThreadDb{}
   
       err := db.Select(&threads, `
   
       SELECT thread.id,thread.name,thread.profile_id,post.id,post.comment,post.profile_id,post.thread_id
   
       FROM thread
   
       INNER JOIN post ON thread.id = post.thread_id
   
       GROUP BY post.id;
   
     `)
   
   
   
       thread_map := make(map[string]Thread)
   
   
   
       for i, thread := range threads {
   
           if _, ok := thread_map[thread.Id]; ok {
   
               thread_map[thread.Id].Posts = append(thread_map[thread.Id].Posts, thread.Post)
   
           } else {
   
               thread_map[thread.Id] = Thread{thread.Id, thread.Name, thread.ProfileId, Post{thread.Post}}
   
           }
   
       }
   
   
   
       var threadSlice string
   
   
   
       for k := range thread_map {
   
           threadSlice = append(threadSlice, k)
   
       }
   
   
   
       if err != nil {
   
           log.Fatal(err)
   
       }
   
       c.JSON(http.StatusOK, gin.H{"data": threadSlice})
   
   }
   
   

   
   


2. Use GROUP_CONCAT or similar. I wouldn't recommend unless you plan on having a maximum of about 100 posts per thread.