Handling of child structs when join equates to null

[psamatt]

I have an application where a user doesnt always have to have a role, therefore the role table can be empty for a given user, the following code represents how i would expect Scany to work, i would expect to have an optional / nullable property in User that would be the role, this i would have thought would allow for this, however i am getting:

scanning: doing scan: scanFn: scany: scan row into struct fields: can't scan into dest[5]: cannot scan NULL into *bool

Here is my application code

type Role struct {
    id string
    name string
    activated bool
}

type User struct {
    id string
    name string
    role *Role  `db:role`
}

sqlQuery := "SELECT u.id, u.name, r.id as "role.id", r.name as "role.name", r.activated as "role.activated" from users u left join role r on r.id = u.role.id where u.id = $1"

var users User
err = pgxscan.ScanOne(&onDemandService, rows, 1)

Is this correct? Am i doing something wrong?

[georgysavva]

Hi, scany doesn't support this type of relationship between structs. This is an ORM feature, and scany isn't an ORM. The only usage for nested/embedded structs in scany is to reuse struct fields. You would need to define your structs in the following way to make them work:

type OptionalRole struct {
    id *string
    name *string
    activated *bool
}

type User struct {
    id string
    name string
    role *OptionalRole  `db:role`
}
// OR
type User struct {
    id string
    name string
    role OptionalRole  `db:role`
}

Scany's purpose is to allow scanning rows into structs in the simplest way possible. Your query says there are 3 optional (NULLable) columns, so your struct definitions need to represent exactly that, even if you use struct field reusing/grouping via nested structs.

I hope this helps. Let me know if you have any questions.