-
Notifications
You must be signed in to change notification settings - Fork 923
-
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!
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 1 comment
-
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.
Beta Was this translation helpful? Give feedback.