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)

type FlatDetails struct {
    Flat    Flat
    Users   []User
}

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

-- name: GetFlatWithUsers :one
SELECT 
  sqlc.embed(f),
  sqlc.embed(u)
FROM
  flats f
JOIN 
  user_flats uf ON f.id = uf.flat_id
JOIN 
  users u ON uf.username = u.username
WHERE 
  f.id = $1;

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

Relevant schema:

CREATE TABLE users (
  username VARCHAR(255) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE flats (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE user_flats (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL REFERENCES users(username),
  flat_id INTEGER NOT NULL REFERENCES 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 :one
SELECT
  sqlc.embed(f),
  sqlc.group(
    sqlc.embed(u)
  )
FROM
  flats f
JOIN 
  user_flats uf ON f.id = uf.flat_id
JOIN 
  users u ON uf.username = u.username
WHERE 
  f.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"
)

const getFlatWithUsers = `-- name: GetFlatWithUsers :one
SELECT
  f.id, f.name,
  u.username, u.email
FROM
  flats f
JOIN
  user_flats uf ON f.id = uf.flat_id
JOIN
  users u ON uf.username = u.username
WHERE
  f.id = $1
`

type GetFlatWithUsersGroup struct {
	Flat  Flat
	Users []User
}

func (data GetAllFlatsWithUsersGroup) HashCode() (string, error) {
	// Imagine we have a proper hashcode generation algorithm
	return fmt.Sprintf("%v", data), nil
}

func (q *Queries) GetFlatWithUsers(ctx context.Context, id int32) (GetFlatWithUsersGroup, error) {
	group := GetFlatWithUsersGroup{}
	rows, err := q.db.Query(ctx, getFlatWithUsers, id)
	if err != nil {
		return group, err
	}
	defer rows.Close()
	hashCode := map[string]bool{}
	for rows.Next() {
		var i GetFlatWithUsersGroup
		var j User
		if err := rows.Scan(
			&i.Flat.ID,
			&i.Flat.Name,
			&j.Username,
			&j.Email,
		); err != nil {
			return group, err
		}
		h, err := i.HashCode()
		if err != nil {
			return group, err
		}
		if len(hashCode) == 0 {
			group = i
			hashCode[h] = true
		}
		if _, ok := hashCode[h]; ok {
			group.Users = append(group.Users, j)
		}
	}
	if err := rows.Err(); err != nil {
		return group, err
	}
	return group, nil
}

And this can be the generated code for :many:

package sql

import (
	"context"
)

const getAllFlatsWithUsers = `-- name: GetAllFlatsWithUsersGroup :many
SELECT
  f.id, f.name,
  u.username, u.email
FROM
  flats f
JOIN
  user_flats uf ON f.id = uf.flat_id
JOIN
  users u ON uf.username = u.username
`

type GetAllFlatsWithUsersGroup struct {
	Flat  Flat
	Users []User
}

func (data GetAllFlatsWithUsersGroup) HashCode() (string, error) {
	// Imagine we have a proper hashcode generation algorithm
	return fmt.Sprintf("%v", data), nil
}

func (q *Queries) GetAllFlatsWithUsers(ctx context.Context) ([]GetAllFlatsWithUsersGroup, error) {
	rows, err := q.db.Query(ctx, getAllFlatsWithUsers)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	items := map[string]GetAllFlatsWithUsersGroup{}
	for rows.Next() {
		var i GetAllFlatsWithUsersGroup
		var j User
		if err := rows.Scan(
			&i.Flat.ID,
			&i.Flat.Name,
			&j.Username,
			&j.Email,
		); err != nil {
			return nil, err
		}
		hashCode, err := i.HashCode()
		if err != nil {
			return nil, err
		}
		if item, ok := items[hashCode]; ok {
			item.Users = append(item.Users, j)
		} else {
			items[hashCode] = i
			i.Users = []User{j}
		}
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	groups := make([]GetAllFlatsWithUsersGroup, 0, len(items))
	for _, item := range items {
		groups = append(groups, item)
	}
	return groups, 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