Return embedded structs as slices when joining and selecting a unique key

[andrewmbenton]

Based on the discussion in this Discord thread which references this SO question about sqlc.embed() and joins.

In the following example, it would be nice if sqlc returned a struct like (note the Users slice)

typeFlatDetailsstruct{FlatFlatUsers []User }

for this query (note the :one in the annotation):

-- name: GetFlatWithUsers :oneSELECTsqlc.embed(f), sqlc.embed(u) FROM flats f JOIN user_flats uf ONf.id=uf.flat_idJOIN users u ONuf.username=u.usernameWHEREf.id= $1;

With an annotation of :many on the above query, sqlc would return a slice of the above FlatDetails struct.

Relevant schema:

CREATETABLEusers ( username VARCHAR(255) PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL ); CREATETABLEflats ( id SERIALPRIMARY KEY, name VARCHAR(255) NOT NULL ); CREATETABLEuser_flats ( id SERIALPRIMARY KEY, username VARCHAR(255) NOT NULLREFERENCES users(username), flat_id INTEGERNOT NULLREFERENCES flats(id) );

I think this should be possible given that flats.id is a unique key. But I'm not sure if this is desirable default behavior. What do other people think?

[KevenGoncalves]

I think it's a very good Idea sometimes we want to push from a join query with one-to-many relations all at once and in a structured way, many tools do that like Prisma, TypeORM

[DavidS-ovm]

one-shot loading of relations is one of the few things where sqlc currently is more awkward to use than a ORM. Arguably that's good because relation pre/auto loading also causes lots of (perf & design) headaches with ORMs.

What I can't tell from your proposal is

1. would this also work for :many to load multiple FlatDetails?
2. would sqlc be able to detect the cardinality of each embed? e.g. what happens if f.id is not unique.

[andrewmbenton]

Good questions.

1. would this also work for :many to load multiple FlatDetails?

I think there's no issue with that, assuming sqlc can infer the many to one relationship which is the whole premise of this idea in the first place. I've edited my original post to clarify.

2. would sqlc be able to detect the cardinality of each embed? e.g. what happens if f.id is not unique.

sqlc can see the column uniqueness constraints, so my assumption was that it could use the information to infer the shape of the output struct. If f.id is not unique then I'd expect sqlc to just do what it does now.

[DavidS-ovm]

makes sense!

[kevinrobayna]

I think the above proposal is fantastic if the query does the joins you can argue that people would expect the relationship to be exposed there.

Currently, if you do something like PrimaryTable.*, sqlc.embed(Secondary) sqlc would generate a struct with Primary properties and a single element of Secondary. Which secondary came through? who knows (especially if there's no order by).

I'd even say that the generated models should include the relationships by default (meaning if I fetch a :one Primary it should not generate a new struct on the queries generated code but just use the model for Primary) Following the example of your tables it should return an instance of.

type FlatDetails struct{Id. String Flat Flat Users []User } 

[RaniAgus]

Would it be possible to do something like this?

-- name: GetFlatWithUsers :oneSELECTsqlc.embed(f), sqlc.group( sqlc.embed(u) ) FROM flats f JOIN user_flats uf ONf.id=uf.flat_idJOIN users u ONuf.username=u.usernameWHEREf.id= $1;

Internally, this could perform the same query as if the fields in .group(...) were written in the SELECT ... list, but then those fields will be grouped by the remaining using a hashcode (yes, I'm a Java dev, sorry!).

For example, this would be the generated code for :one:

package sql import ( "context" ) constgetFlatWithUsers=`-- name: GetFlatWithUsers :oneSELECT f.id, f.name, u.username, u.emailFROM flats fJOIN user_flats uf ON f.id = uf.flat_idJOIN users u ON uf.username = u.usernameWHERE f.id = $1`typeGetFlatWithUsersGroupstruct{FlatFlatUsers []User } func (dataGetAllFlatsWithUsersGroup) HashCode() (string, error){// Imagine we have a proper hashcode generation algorithmreturnfmt.Sprintf("%v", data), nil } func (q*Queries) GetFlatWithUsers(ctx context.Context, idint32) (GetFlatWithUsersGroup, error){group:=GetFlatWithUsersGroup{} rows, err:=q.db.Query(ctx, getFlatWithUsers, id) iferr!=nil{returngroup, err } deferrows.Close() hashCode:=map[string]bool{} forrows.Next(){variGetFlatWithUsersGroupvarjUseriferr:=rows.Scan( &i.Flat.ID, &i.Flat.Name, &j.Username, &j.Email, ); err!=nil{returngroup, err } h, err:=i.HashCode() iferr!=nil{returngroup, err } iflen(hashCode) ==0{group=ihashCode[h] =true } if_, ok:=hashCode[h]; ok{group.Users=append(group.Users, j) } } iferr:=rows.Err(); err!=nil{returngroup, err } returngroup, nil }

And this can be the generated code for :many:

package sql import ( "context" ) constgetAllFlatsWithUsers=`-- name: GetAllFlatsWithUsersGroup :manySELECT f.id, f.name, u.username, u.emailFROM flats fJOIN user_flats uf ON f.id = uf.flat_idJOIN users u ON uf.username = u.username`typeGetAllFlatsWithUsersGroupstruct{FlatFlatUsers []User } func (dataGetAllFlatsWithUsersGroup) HashCode() (string, error){// Imagine we have a proper hashcode generation algorithmreturnfmt.Sprintf("%v", data), nil } func (q*Queries) GetAllFlatsWithUsers(ctx context.Context) ([]GetAllFlatsWithUsersGroup, error){rows, err:=q.db.Query(ctx, getAllFlatsWithUsers) iferr!=nil{returnnil, err } deferrows.Close() items:=map[string]GetAllFlatsWithUsersGroup{} forrows.Next(){variGetAllFlatsWithUsersGroupvarjUseriferr:=rows.Scan( &i.Flat.ID, &i.Flat.Name, &j.Username, &j.Email, ); err!=nil{returnnil, err } hashCode, err:=i.HashCode() iferr!=nil{returnnil, err } ifitem, ok:=items[hashCode]; ok{item.Users=append(item.Users, j) } else{items[hashCode] =ii.Users= []User{j} } } iferr:=rows.Err(); err!=nil{returnnil, err } groups:=make([]GetAllFlatsWithUsersGroup, 0, len(items)) for_, item:=rangeitems{groups=append(groups, item) } returngroups, nil }

There is one limitation: you can use only one sqlc.group() statement per query (in a similar fashion as a GROUP BY statement in sql).

[Koshkaj]

@andrewmbenton Can this be escalated? It is a pretty useful and important functionality

[percybolmer]

+1

[eugen-bondarev]

+1

[ainsleyclark]

Is there any workaround for the moment? Is this being pursued currently?