Best Practices around using 'to many' joined data?

[aarondl]

When you have a query like the following:

select *
from users
left join comments on comments.user_id = users.id

How do people deal with this? It seems like sqlc's result is:

type UsersRow struct {
   UserCol int
   CommentCol string
  ...
}

I think intuitively in Go you'd rather a result like so:

type UsersRow struct {
   Comments []CommentRow
}

It's not obvious from the docs how to handle this common use case. Is it expected that users collect all the user ids returned from a first query that only fetches users, then subsequently perform another query where user_id = any($1::[]integer) instead of joining?

Just trying to solve this for the general case because I'm finding it a bit clunky. It's not trivial (boilerplate-wise) to convert the sqlc result into the desired result (need reflect helpers or large conversion functions), but it requires many more queries to the database.

I feel like I'm missing something. Can people share how they're using sqlc to retrieve and use data like the above?

[kyleconroy]

We're still deciding on how best to embed nested structs in return values. Please see the discussion here #363

To answer your specific question: sqlc will also return a struct that mirrors the results from the database. Let's assume that your tables look like this:

CREATE TABLE users (
  id BIGSERIAL NOT NULL,
  name TEXT
);

CREATE TABLE comments (
  user_id BIGINT NOT NULL,
  body TEXT
);

And this is the query you

select *
from users
left join comments on comments.user_id = users.id

With a bit of sample data, this returns the following data.

| id | name | user_id |  body |
|----|------|---------|-------|
|  1 |  foo |       1 |   hey |
|  2 |  bar |       2 |   you |
|  2 |  bar |       2 | there |

Which translates into the final Go struct.