Am I missing how to model one to many relationships with sqlc.embed?

[austincollinpena]

What do you want to change?

Given this query:

-- name: CategoryGetAllAndChildren :many
SELECT swipe_category.*,
       sqlc.embed(swipe_sub_category)
FROM swipe_category
         LEFT JOIN swipe_sub_category on swipe_category.swipe_category_id = swipe_sub_category.swipe_category_id
WHERE swipe_category.organization_id = $1;

Where each swipe_sub_category has a foreign key relationship to category, I would expect the generated struct to be this:

type CategoryGetAllAndChildrenRow struct {
    SwipeCategoryID  string           `db:"swipe_category_id" json:"swipeCategoryID"`
    CreatedAt        pgtype.Timestamp `db:"created_at" json:"createdAt"`
    OrganizationID   string           `db:"organization_id" json:"organizationID"`
    Name             string           `db:"name" json:"name"`
        // This field would be a slice
    SwipeSubCategory **[]SwipeSubCategory** `db:"swipesubcategory" json:"swipesubcategory"`
}

and not this:

type CategoryGetAllAndChildrenRow struct {
    SwipeCategoryID  string           `db:"swipe_category_id" json:"swipeCategoryID"`
    CreatedAt        pgtype.Timestamp `db:"created_at" json:"createdAt"`
    OrganizationID   string           `db:"organization_id" json:"organizationID"`
    Name             string           `db:"name" json:"name"`
        // Not just a field
    SwipeSubCategory SwipeSubCategory `db:"swipesubcategory" json:"swipesubcategory"`
}

Am I missing how to use this feature?

What database engines need to be changed?

PostgreSQL

What programming language backends need to be changed?

Go

[joshm91]

I really like sqlc but every time I come back to it I run into this use case. Supporting it natively would be great!

[alimoli]

Any news?

[denialanderror]

This isn't what sqlc.embed does. Embedded structs are just a way to allow for code reuse of common collections of fields. If it were otherwise and the generated code took the returned rows and manipulated them to produce a struct with a one-to-many relationship, that function would then return fewer rows than you would expect from the SQL.

sqlc is just a library to generate type-safe SQL that output the returned queries as named structs. It's helpful boilerplate generation but its still just SQL and row scanning under the hood. It's not an ORM.

[Luke-zhang-04]

Related issues regarding left joins #2348 and #2997 could be a good start. Also see related discussion #2643.