Document sqlc behavior for queries with JOINs

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.

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

@kyleconroy

@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.

@kylecarbs

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

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

Bump - and progress here?

Bump again - any updated?

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

bump?

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

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

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?

Navigation Menu

Repository navigation

Document sqlc behavior for queries with JOINs #643

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Document sqlc behavior for queries with JOINs #643

Description

Activity

fr3fou commented on Nov 5, 2020

kylecarbs commented on Apr 26, 2021

kyleconroy commented on Aug 28, 2021

kyleconroy commented on Aug 28, 2021

arshbot commented on May 10, 2022

cholazzzb commented on May 11, 2023

KaviiSuri commented on May 22, 2023

KenanHH commented on Jul 13, 2023

DeedleFake commented on Aug 22, 2023

kyleconroy commented on Sep 22, 2023

rssistemasitu commented on Feb 17, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions