Document sqlc behavior for queries with JOINs

[georgysavva]

Hi. Thanks for a great project. The idea is really promising and fresh for the Go ecosystem!

One thing wasn't clear for me from the existing documentation and examples:
How sqlc handles relations and complex queries with JOINs, does it still uses flat structures for the output or it somehow reuses and embeds structs that represent a single table.

After going through existing issues I found out that sqlc goes with the simplest approach and generates a flat structure for every query even a complex one with a JOIN.

I think you should explicitly document it or add an example with a JOIN, because maybe not only me was looking for something like that.

[fr3fou]

Were the docs added? I'm wondering the same thing

[kylecarbs]

@kyleconroy I'd love to work on this. Before I do, I'd love your thoughts on doing the following:

-- schema.sql
CREATE TABLE users (id text);
CREATE TABLE usernames (user_id text, name text);

-- query.sql
-- name: GetUsersWithNames :many
SELECT users.*, usernames.name FROM users JOIN usernames ON users.id = usernames.user_id;

Currently, this would generate a struct like:

type GetUsersWithNamesRow struct {
	ID string
	Name string
}

I'm wondering if we could embed existing structs instead... like:

type GetUsersWithNamesRow struct {
	User
	Name string
}

This would enable complex cross-table queries sharing structs.

[kyleconroy]

@kylecarbs Please see #363 for a discussion about embedding structs. It's a bit more complicated that it seems.

[kyleconroy]

This is doubly important now that #983 has been merged.

[arshbot]

Bump - and progress here?

[cholazzzb]

Bump again - any updated?

[KaviiSuri]

now that we have sqlc.embed merged, why is it not documented in the docs? I'd love to help out if needed, but we need to add documentation for it

[KenanHH]

bump?

[DeedleFake]

It's not in the main documentation, but there's a quick summary of the new sqlc.embed() in the changelog.

[kyleconroy]

Sorry for the lack of documentation. We added a dedicated section for this in the last release.

https://docs.sqlc.dev/en/stable/howto/embedding.html

[rssistemasitu]

I'm trying to create a query with LEFT JOIN, but a query parameter with 13 entries is generated, but my query only has 11 entries.
Then I get the error: "sql: expected 11 arguments, got 13"

My query:

SELECT sqlc.embed(u), sqlc.embed(r)
FROM users u
LEFT JOIN user_roles ur ON u.id = ur.user_id
LEFT JOIN roles r ON ur.role_id = r.id
WHERE ((u.created_at BETWEEN ? AND ?) OR (? = '' AND ? = '') OR (? IS NULL AND ? IS NULL))
AND ((u.is_active = ?) OR (? = '') OR (? IS NULL))
ORDER BY u.created_at DESC
LIMIT ? 
OFFSET ?;

Generated parameter:

type SearchUsersParams struct {
    FromCreatedAt   time.Time   `json:"from_created_at"`
    FromCreatedAt_2 time.Time   `json:"from_created_at_2"`
    ToCreatedAt     time.Time   `json:"to_created_at"`
    ToCreatedAt_2   time.Time   `json:"to_created_at_2"`
    Column5         interface{} `json:"column_5"`
    Column6         interface{} `json:"column_6"`
    Column7         interface{} `json:"column_7"`
    Column8         interface{} `json:"column_8"`
    IsActive        bool        `json:"is_active"`
    Column10        interface{} `json:"column_10"`
    Column11        interface{} `json:"column_11"`
    Limit           int32       `json:"limit"`
    Offset          int32       `json:"offset"`
}

My classes:

type Role struct {
	ID        string    `json:"id"`
	Name      string    `json:"name"`
	IsActive  bool      `json:"is_active"`
	CreatedAt time.Time `json:"created_at"`
	UpdatedAt time.Time `json:"updated_at"`
}

type User struct {
	ID                string    `json:"id"`
	NickName          string    `json:"nick_name"`
	Email             string    `json:"email"`
	Password          string    `json:"password"`
	VerificationEmail bool      `json:"verification_email"`
	IsActive          bool      `json:"is_active"`
	CreatedAt         time.Time `json:"created_at"`
	UpdatedAt         time.Time `json:"updated_at"`
}

type UserRole struct {
	UserID sql.NullString `json:"user_id"`
	RoleID sql.NullString `json:"role_id"`
}

It appears that sqlc is generating an incorrect amount of query inputs.
Please, can you help me?