[v5] Preload nullable() foreign keys

[steschwa]

I'm using following DB migrations and models (demo but reproduces my problem):

Migrations

xxx_User.ts

public async up() {
    this.schema.createTable(this.tableName, table => {
        table.increments("id")
        table.integer("setting_id").nullable().unsigned()

        table
            .foreign("setting_id")
            .references("id")
            .inTable("settings")
            .onDelete("SET NULL")
            .onUpdate("CASCADE")
    })
}

xxx_Setting.ts

public async up() {
    this.schema.createTable(this.tableName, table => {
        table.increments("id")
        table.string("key", 50).notNullable()
        table.string("value", 50).notNullable()
    })
}

Models

User.ts

export default class User extends BaseModel {
    @column({ isPrimary: true })
    public id: number

    @column()
    public settingId?: number

    /// Relationships

    @belongsTo(() => Setting)
    public setting: BelongsTo<typeof Setting>
}

Setting.ts

export default class Setting extends BaseModel {
    @column({ isPrimary: true })
    public id: number
}

Controller

public async userSettings() {
    const user: User // User with `setting_id = null`
 
    await user.preload("setting")

    return {
        user
    }
}

The problem occurs if i want to preload a User record with setting_id = null.
I get the response Cannot preload "setting", value of "User.settingId" is undefined. Make sure to set "null" as the default value for foreign keys which is plausible because some can't preload when there is no value.

But how do i set the default foreign key to null?

[steschwa]

Checking for existence before preloading bypasses the error:

if(user.settingId) {
    await user.preload("setting")
}

Is this the intended way?

[arthurcvm]

Hi! You solve this? I have same problem in .load() function, this not verify if the foreign key is not null.

[steschwa]

I think the best solution is to set the foreign key on the settings table instead. As suggested by @hariprasath-yadav

[arthurcvm]

In my case the solution of @hariprasath-yadav not make sense, because my foreign key is null in database but I receive the error message, this is the solution that worked for me:

await permission.refresh()
await permission.load((loader) => {
     loader.preload('parent_permission').preload('system')
 })

[hariprasath-yadav]

My suggestion is
don't create a users table with a foreign key of a settings table
create a settings table with a foreign key of a users table
and on preload, it will not show any error

[AhmadPirzargar]

I set directly on the model value to null and this work for me :)

@column({ columnName: 'shopId' }) public shopId: number | null = null