Nested slice of struct type in sqlc, postgres and golang

The reason seems to be that a post may have a single author (this is why it's singular), but there may be multiple votes on the same post, hence the plurality. A way to cope with that would be to have an aggregate table (or view, if that works as well) for voting summary to posts and have a foreign key from posts to this aggregate vote table, ensuring that you have a single aggregate vote. If that can be a view, then you just need a select query to define the view so you get the proper result. Otherwise you may need to maintain the table when votes are cast.

You can achieve this by creating an intermediary view that converts the votes to json. You can then use the overrides to cast this back to a slice of the Vote struct. For example.

CREATE TABLE users (
  id SERIAL NOT NULL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  username TEXT NOT NULL UNIQUE,
  password TEXT NOT NULL,
  image TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE posts (
  id SERIAL NOT NULL PRIMARY KEY,
  title TEXT NOT NULL,
  content JSONB,
  subreddit_id INT NOT NULL REFERENCES subreddits(id) ON DELETE CASCADE,
  author_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE votes (
  id SERIAL NOT NULL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type vote_type NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  UNIQUE(user_id, post_id)
);

CREATE VIEW post_view AS
SELECT
    p.*,
    JSON_AGG(u.*) AS author,
    JSON_AGG(v.*) AS votes
FROM
    posts p
JOIN
    users u ON p.author_id = u.id
JOIN
    votes v ON p.id = v.post_id
WHERE
    p.id = $1
GROUP BY
    p.id, u.id;

This simplifies the query to

-- name: post_view :one
SELECT * FROM post_view WHERE id = $1;

Now you need to override the types of your post_view columns in your sqlc.(yaml|yml) or sqlc.json file, I use json in this example.

{
  "version": "2",
  "sql": [
    {
      "schema": "schema.sql",
      "queries": "query.sql",
      "engine": "postgresql",
      "gen": {
        "go": {
          "sql_package": "pgx/v5",
          "out": "db",
          "emit_json_tags": true,
          "emit_db_tags": true,
          "overrides": [
             {
              "column": "post_view.content",
              "go_type": {
                "import": "sql",
                "type": "NullRawMessage"
              }
            },
            {
              "column": "post_view.votes",
              "go_type": {
                "type": "Vote",
                "slice": true
              }
            },
            {
              "column": "post_view.author",
              "go_type": {
                "type": "User"
              }
            }            
          ]
        }
      }
    }
  ]
}

This should give you a model like so, with the correct []Vote slice on the PostView struct, along with the User for the Author, etc.

type PostView struct {
    ID          int32              `db:"id" json:"id"`
    Title       string             `db:"title" json:"title"`
    Content     sql.NullRawMessage `db:"content" json:"content"`
    SubredditID int32              `db:"subreddit_id" json:"subreddit_id"`
    AuthorID    int32              `db:"author_id" json:"author_id"`
    CreatedAt   pgtype.Timestamp   `db:"created_at" json:"created_at"`
    UpdatedAt   pgtype.Timestamp   `db:"updated_at" json:"updated_at"`
    Author      User               `db:"author" json:"author"`
    Votes       []Vote             `db:"votes" json:"votes"`
}

I made a working playground for all this here https://play.sqlc.dev/p/024fe40a264802366856de1713086382669e7d88c7bd9f480b4168196a807e29