Support joins one to many (is the project still active?)

[alimoli]

What do you want to change?

I am quite surprised not to see the possibility to generate from a schema that defines a one-to-many relation (considering many people are using sqlc in production for real-world applications). I would expect to get a slice if a joined table has a one-to-many relation.

Related to https://github.com/sqlc-dev/sqlc/issues/3144 .

What database engines need to be changed?

PostgreSQL, MySQL, SQLite

What programming language backends need to be changed?

Go

[DogAndHerDude]

Been banging my head against the wall for an hour with this as well, using SQLite. As I understand the only way is to just go with json_group_array(json_object(...)) and then unmarshal it yourself. Which beats the purpose of sqlc.

[percybolmer]

Came here searching for this answer.

-- name: GetPostsWithTags :many
SELECT sqlc.embed(posts), sqlc.embed(post_tags)
FROM posts
JOIN post_tags ON post_tags.post_id = posts.id
WHERE posts.published = true
ORDER BY created_at
LIMIT :limit OFFSET :offset;

This Join returns multiple Post tags but the generated struct

type GetPostsWithTagsRow struct {
    Post    Post
    PostTag PostTag
}

But expected would be

type GetPostsWithTagsRow struct {
    Post    Post
    PostTag []
PostTag
}

I understand that it might be hard to know if the intention is multiple or singular responses based on the SQL code. But maybe a sqlc.JoinMultiple or something?

Or that the Default for an JOIN should be an Array, since it can always return multiple items if not using a ID in the Where of the join.

[ainsleyclark]

+1 on this, I'm not sure how this works, always one type is generated instead of a collection. I'm presuming this is the same for all engines?

[jcantonio]

I also really would like to see a solution to this recurrent issue. The only workaround for now is the view option which introduces more code to migrate tables. Otherwise sqlc is an amazing generator.

[bkleef]

We’re running into this same limitation now and were surprised to find that this currently isn’t supported. Has there been any update on when this might be worked on? We’d love to know if it’s on the roadmap.