-
Notifications
You must be signed in to change notification settings - Fork 934
-
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?
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 1 comment
-
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.
Beta Was this translation helpful? Give feedback.