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?