Left join which results in a null embed struct throws sql: Scan error

[ayaanqui]

I have a relatively simple query that returns all events with a join on 2 tables.

-- name: FindAllEvents :many
SELECT
    sqlc.embed(event),
    sqlc.embed(p),
    sqlc.embed(u)
FROM "event"
JOIN "participant" p ON p.event_id = "event".id
LEFT JOIN "user" u ON u.id = p.user_id;

This query generates the following code:

package database

type FindAllEventsRow struct {
	Event       Event       `db:"event" json:"event"`
	Participant Participant `db:"participant" json:"participant"`
	User        User        `db:"user" json:"user"`
}

func (q *Queries) FindAllEvents(ctx context.Context) ([]FindAllEventsRow, error) {
	...
	var items []FindAllEventsRow
	for rows.Next() {
		var i FindAllEventsRow
		if err := rows.Scan(
			&i.Event.ID,
			...,
			&i.Participant.ID,
			...,
			&i.User.ID,
			&i.User.Name,
			...,
		); err != nil {
			return nil, err
		}
		items = append(items, i)
	}
	...
	return items, nil
}

The problem with this generated code is that the FindAllEventsWithUserRow.User field could be nil since the participant.user_id column is nullable. However, since the generated code doesn't take that into account and tries to scan &i.User.ID we get the following runtime error:

sql: Scan error on column index 20, name "id": converting NULL to int64 is unsupported

Is there any way this can be fixed or addressed? Thanks!

[ayaanqui]

After doing a bunch of searching I was able to find this suggestion #2997 (comment) which creates a Postgres VIEW which does the joining.

In my case, this is would be the solution:

-- Create the view below in a migration file
CREATE VIEW participant_user AS (
    SELECT 
        "participant".*,
        "user"."name" AS "user_name",
        "user"."email" AS "user_email",
        "user"."image_url" AS "user_image_url"
    FROM "participant"
    LEFT JOIN "user" ON "user"."id" = "participant"."user_id"
);

-- name: FindAllEvents :many
SELECT
    sqlc.embed(event),
    sqlc.embed(p),
FROM "event"
JOIN "participant_user" p ON p.event_id = "event".id;

It would be helpful if sqlc could determine that nullable refs should always use the sql.Null* type, but at least there is a way to get that effect, granted you have to use views.