Embedded Structs with sqlc

embed.sql

-- name: ContactsByCustomerID :many
SELECT sqlc.embed(contacts),
       sqlc.embed(customers),
       sqlc.embed(addresses)
FROM
  (
    SELECT *
    FROM addresses
    WHERE addresses.id = contacts.address_id
  ) AS a,
  contacts
JOIN customers ON (contacts.customer_id = customers.id)
WHERE customer_id = @customer_id
AND contacts.deleted_at IS NULL
ORDER BY id ASC
LIMIT @_limit OFFSET @_offset;

gen.sql.go

type ContactsByCustomerIDParams struct {
	CustomerID int64 `db:"customer_id"`
	Offset     int32 `db:"_offset"`
	Limit      int32 `db:"_limit"`
}

type ContactsByCustomerIDRow struct {
	Contact  Contact
	Customer Customer
	Address  Address
}

type Address struct {
	ID         int64       `db:"id"`
	TenantID   int64       `db:"tenant_id"`
	Line1      string      `db:"line_1"`
	Line2      zero.String `db:"line_2"`
	City       zero.String `db:"city"`
	State      zero.String `db:"state"`
	Country    zero.String `db:"country"`
	PostalCode zero.String `db:"postal_code"`
	CreatedAt  time.Time   `db:"created_at"`
	UpdatedAt  time.Time   `db:"updated_at"`
	DeletedAt  zero.Time   `db:"deleted_at"`
}

type Contact struct {
	ID           int64       `db:"id"`
	TenantID     int64       `db:"tenant_id"`
	CustomerID   int64       `db:"customer_id"`
	FirstName    string      `db:"first_name"`
	LastName     string      `db:"last_name"`
	PhoneNumber  zero.String `db:"phone_number"`
	EmailAddress zero.String `db:"email_address"`
	AddressID    zero.Int    `db:"address_id"`
	CreatedAt    time.Time   `db:"created_at"`
	UpdatedAt    time.Time   `db:"updated_at"`
	DeletedAt    zero.Time   `db:"deleted_at"`
}

type Customer struct {
	ID       int64 `db:"id"`
	TenantID int64 `db:"tenant_id"`
	// A human-friendly ID to show on quotes/invoices/etc.
	AlternateID zero.String `db:"alternate_id"`
	Name        zero.String `db:"name"`
	CreatedAt   time.Time   `db:"created_at"`
	UpdatedAt   time.Time   `db:"updated_at"`
	DeletedAt   zero.Time   `db:"deleted_at"`
}

const contactsByCustomerID = `-- name: ContactsByCustomerID :many
SELECT contacts.id, contacts.tenant_id, contacts.customer_id, contacts.first_name, contacts.last_name, contacts.phone_number, contacts.email_address, contacts.address_id, contacts.created_at, contacts.updated_at, contacts.deleted_at, customers.id, customers.tenant_id, customers.alternate_id, customers.name, customers.created_at, customers.updated_at, customers.deleted_at, addresses.id, addresses.tenant_id, addresses.line_1, addresses.line_2, addresses.city, addresses.state, addresses.country, addresses.postal_code, addresses.created_at, addresses.updated_at, addresses.deleted_at
FROM
(select id, tenant_id, line_1, line_2, city, state, country, postal_code, created_at, updated_at, deleted_at from addresses where addresses.id = contacts.address_id) as a,
contacts
JOIN customers ON (contacts.customer_id = customers.id)
WHERE customer_id = $1
AND contacts.deleted_at IS NULL
ORDER BY id ASC
LIMIT $3 OFFSET $2
`

func (q *Queries) ContactsByCustomerID(ctx context.Context, arg ContactsByCustomerIDParams) ([]ContactsByCustomerIDRow, error) {
	rows, err := q.db.QueryContext(ctx, contactsByCustomerID, arg.CustomerID, arg.Offset, arg.Limit)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var items []ContactsByCustomerIDRow
	for rows.Next() {
		var i ContactsByCustomerIDRow
		if err := rows.Scan(
			&i.Contact.ID,
			&i.Contact.TenantID,
			&i.Contact.CustomerID,
			&i.Contact.FirstName,
			&i.Contact.LastName,
			&i.Contact.PhoneNumber,
			&i.Contact.EmailAddress,
			&i.Contact.AddressID,
			&i.Contact.CreatedAt,
			&i.Contact.UpdatedAt,
			&i.Contact.DeletedAt,
			&i.Customer.ID,
			&i.Customer.TenantID,
			&i.Customer.AlternateID,
			&i.Customer.Name,
			&i.Customer.CreatedAt,
			&i.Customer.UpdatedAt,
			&i.Customer.DeletedAt,
			&i.Address.ID,
			&i.Address.TenantID,
			&i.Address.Line1,
			&i.Address.Line2,
			&i.Address.City,
			&i.Address.State,
			&i.Address.Country,
			&i.Address.PostalCode,
			&i.Address.CreatedAt,
			&i.Address.UpdatedAt,
			&i.Address.DeletedAt,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	if err := rows.Close(); err != nil {
		return nil, err
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return items, nil
}