Embedding struct? · sqlc-dev/sqlc · Discussion #363

farwydi
Feb 27, 2020

Hello!

Input: (PostgreSQL)

-- name: PreFetchContentsByGroup :many
SELECT c.*, array_agg(g.id)::INT[] AS Ids
FROM contents c
 INNER JOIN content_group_links cgl ON c.id = cgl.content_id
 INNER JOIN groups g ON cgl.group_id = g.id
WHERE g.id = ANY ($1::INT[])
GROUP BY c.id;

const preFetchContentsByGroup = `-- name: PreFetchContentsByGroup :many
SELECT c.id, c.name, c.type, c.file, c.category, c.description, c.priority, c.enable, c.sys_1, c.sys_2, c.text_field_1, c.text_field_2, c.text_field_3, c.text_field_4, c.name_i18n, c.protected, c.uidx, c.name_i, c.description_i, c.text_field_1_i, c.text_field_2_i, c.text_field_3_i, c.text_field_4_i, c.file_i, array_agg(g.id)::INT[] AS Ids
FROM contents c
 INNER JOIN content_group_links cgl ON c.id = cgl.content_id
 INNER JOIN groups g ON cgl.group_id = g.id
WHERE g.id = ANY (1ドル::INT[])
GROUP BY c.id
`

Output:

type PreFetchContentsByGroupRow struct {
	ID int32
	Name sql.NullString
	Type string
	File uuid.UUID
	Category sql.NullInt32
	Description sql.NullString
	Priority int16
	Enable bool
	Sys1 sql.NullString
	Sys2 sql.NullString
	TextField1 sql.NullString
	TextField2 sql.NullString
	TextField3 sql.NullString
	TextField4 sql.NullString
	NameI18n sql.NullInt32
	Protected bool
	Uidx uuid.UUID
	NameI sql.NullString
	DescriptionI sql.NullString
	TextField1I sql.NullString
	TextField2I sql.NullString
	TextField3I sql.NullString
	TextField4I sql.NullString
	FileI sql.NullString
	Ids []int32
}

Is it possible to do so ...

type PreFetchContentsByGroupRow struct {
	Content
	Ids []int32
}

Can this be?

Answered by andrewmbenton

Aug 11, 2023

As of v1.18.0 you can use sqlc.embed() for this: https://docs.sqlc.dev/en/stable/reference/changelog.html#sqlc-embed

View full answer

Replies: 10 comments 6 replies

kyleconroy
Mar 9, 2020
Maintainer

Thank you for the feature request. I agree that the PreFetchContentsByGroupRow generated struct isn't very useful for this given query. However, right now I don't have any plans on returning nested structures from queries.

If it is added in the future, it will probably work as you've outlined. c.* expressions would be scanned into separate structs on the return row.

For others reading this issue, feel free to post your own queries that could be helped by embedding structs in the return result (or just hit the 👍 button)

0 replies

inconshreveable
Mar 29, 2020

Here's a proposed spec for modified star expansion in generated Go code to satisfy this feature request:

Stating this as three simple rules:

If the only returned value of a query is table_name.* then there is no behavior change.
Otherwise, each occurrence of table_name.* is expanded to its model struct in the result row struct.
As a special case, * now expands to table_1.*, table_2.*, table_3.* for each table in the query

Here are a set of examples illustrating the proposed behavior changes:

SELECT * FROM table_a -> remains unchanged
SELECT table_a.* FROM table_a -> remains unchanged
SELECT table_a.* FROM table_a, table_b -> remains unchanged
SELECT table_a.*, LENGTH(table_a.foo) FROM table_a -> now outputs a row struct with an embedded model for table_a as well as any other columns selected:

type Row struct {
 TableA TableA
 Col_2 int
}

SELECT table_a.*, table_b.foo FROM table_a, table_b -> this functions like the previous example outputting a row struct of:

type Row struct {
 TableA TableA
 Foo int
}

SELECT table_a.*, table_b.* FROM table_a, table_b -> now outputs a row struct with embedded models for both table_a and table_b:

type Row struct {
 TableA TableA
 TableB TableB
}

SELECT * FROM table_a, table_b -> this is equivalent to the previous example. the * is expanded to table_a.*, table_b.*:

type Row struct {
 TableA TableA
 TableB TableB
}

0 replies

kyleconroy
Mar 29, 2020
Maintainer

Thanks for writing up a spec. I want to have an agreed upon design before any code is written.

As designed, this is a breaking change. The final spec should be opt-in, either in the configuration file or on a per-query basis.

This proposal doesn't play well with CTEs and other nested queries. Given this query:

WITH foobar as (
 SELECT foo.*, bar.* FROM foo, bar
), subquery as (
 SELECT foobar.*, foobar.* FROM foobar
)
SELECT subquery.* FROM subquery

would we expect the output to be?

type Foobar struct {
 Foo Foo
 Bar Bar
}
type Subquery struct {
 Foobar Foobar
 Foobar_1 Foobar
}
type Row struct {
 Subquery Subquery
}

My proposal is that we introduce a sqlc function that explicitly signals that a star expansion should be embedded.

-- name: FooLength :many
SELECT sqlc.embed(foo.*), LENGTH(foo.bar) as bar_len FROM foo;
-- name: FooBaz :many
SELECT sqlc.embed(foo.*), bar.baz FROM foo, bar;
-- name: FooBar :many
SELECT sqlc.embed(foo.*), sqlc.embed(bar.*) FROM foo, bar;

// FooLength return value
type Row struct {
 Foo Foo
 BarLen int32 
}
// FooBaz return value
type Row struct {
 Foo Foo
 Baz int
}
// FooBar return value
type Row struct {
 Foo Foo
 Bar Bar
}

Note that in this proposal, the behavior for SELECT * FROM foo, bar; would not change.

In the future, I'd like to support the same feature using composite types, which is part of the SQL standard. Sadly, the lib/pq driver and database/sql packages don't support composite types because they don't use the binary protocol for PostgreSQL. It's possible that we should wait on implementing this feature and instead focus on adding support for pgx. With composite type support, the queries would be much cleaner:

-- name: FooLength :many
SELECT foo, LENGTH(foo.bar) as bar_len FROM foo;
-- name: FooBaz :many
SELECT foo, bar.baz FROM foo, bar;
-- name: FooBar :many
SELECT foo, bar FROM foo, bar;

Let me play around with pgx composite type support.

0 replies

inconshreveable
May 22, 2020

Just to follow up here we've run into another situation where this would be beneficial. It's a standard many-to-many relationship where we want to pull out a list of objects and all of objects that they're related to via a join table. Given sqlc's examples, it's basically this query:

SELECT author.*, book.* FROM author
JOIN author_to_books
JOIN books
WHERE author.first_name = 'John'

Instead of doing this the way we'd like we're going to do an application level join instead (select all the books, select all the authors, stitch them together in memory).

Re your questions:

This proposal doesn't play well with CTEs and other nested queries. Given this query:
would we expect the output to be?

I'd expect more complex query constructions like CTEs to continue with their existing output behavior (all rows combined) until there's time/demand to add the additional smarts to the codegen to propagate typing through where possible.

Looking at this a second time, I think these might be orthogonal concerns. Does your sqlc.embed() proposal solve that problem with your nested CTE example? e.g. if I embed sqlc.embed() into a CTE nested a few CTEs deep, will it be any less hard to propagate that marker through the AST? I'm definitely not expert enough here to understand the implementation nuances, but it appears like these might be two unrelated problems:

Should star expansion yield typed table definitions in the codegen be explicit (sqlc.embed or implicit?)
Implementing sql table -> output type mapping choice propagation through nested CTEs or subqueries

0 replies

troylelandshields
Oct 22, 2021

Definitely would love this.

SELECT p.*, c.course_id FROM backend.product p
INNER JOIN backend.decision_tool_course_products c on p.id = c.product_id
WHERE c.course_id = @course_id::uuid
AND p.type = @product_type