2

I am using sqlc with a PostgreSQL database in a Golang project. My database has the following simplified schema:

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

This is the query that i have written so sqlc can generate code from it:

-- name: FindPostWithAuthorAndVotes :one
SELECT posts.*, sqlc.embed(users), sqlc.embed(votes)
FROM posts
  INNER JOIN users ON users.id = posts.author_id
  INNER JOIN votes ON votes.post_id = posts.id
WHERE posts.id = $1;

what i expect to have in the generated type in go code is:

type FindPostWithAuthorAndVotesRow struct {
    ID          int32                `json:"id"`
    Title       string               `json:"title"`
    Content     sql.NullRawMessage   `json:"content"`
    SubredditID int32                `json:"subredditId"`
    AuthorID    int32                `json:"authorId"`
    CreatedAt   time.Time            `json:"createdAt"`
    UpdatedAt   time.Time            `json:"updatedAt"`
    User        User                 `json:"user"`
    Vote        []Vote               `json:"vote"`
}

but what i get is:

type FindPostWithAuthorAndVotesRow struct {
    ID          int32                `json:"id"`
    Title       string               `json:"title"`
    Content     types.NullRawMessage `json:"content"`
    SubredditID int32                `json:"subredditId"`
    AuthorID    int32                `json:"authorId"`
    CreatedAt   time.Time            `json:"createdAt"`
    UpdatedAt   time.Time            `json:"updatedAt"`
    User        User                 `json:"user"`
    Vote        Vote                 `json:"vote"`
}

i want the Vote field's type to be a slice of Vote. but i get it as just Vote struct. using sqlc.embed(votes) is not working. is there a way to make it work?

zorojuro
  • 21
  • 1
  • From your SQL, there's no special treatment of `votes`. If there are 10 `votes` records that have same `post_id`, then you'd get 10 rows back. You must either aggregate them into one row on DB side (with jsonb type maybe?) or do so on Go side. – Quang Hoang Aug 31 '23 at 18:21
  • I did a `JSON_AGG(votes.*) AS votes` also. that generated a `json.RawMessage` type for the `Votes` field in the generated struct. is there no way to get sqlc to generate `[]Vote` type? – zorojuro Sep 01 '23 at 15:38
  • That's expected because `json_agg` returns a `json.RawMessage`. You would probably need to override this in the config. An example can be seen in [the doc](https://docs.sqlc.dev/en/stable/reference/datatypes.html#json). – Quang Hoang Sep 01 '23 at 17:43
  • The example in the doc refers to overriding a `jsonb` type column. That is not the same as querying a json aggregated column whose type is just `json`. Anyways I have written a custom transformer for each sql query where I query aggregated columns, which is kind of inefficient with respect to scaling. But anyways thanks for chipping in. – zorojuro Sep 02 '23 at 17:38

1 Answers1

0

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.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
  • i tried with aggregation also using JSON_AGG. please refer to my reply comment in the question block – zorojuro Sep 01 '23 at 15:40
  • @zorojuro can you edit your question with the details? – Lajos Arpad Sep 01 '23 at 16:18
  • What I meant was refer the comment section in the question. But anyways since I am not able to find a solution within sqlc, I am now doing the transformation in my go code like I mentioned in the comments directly under the question. Thanks anyways. – zorojuro Sep 02 '23 at 17:40