number field with unique and nullable index does not update with undefined value

[leocabeza]

I have a number field which is nullable and unique, I try to update it with the instance merge method of the model, but it does not updates, maybe it's a typing issue, since passing explicitily a null value does set is as null.

Migration:

import BaseSchema from '@ioc:Adonis/Lucid/Schema'

export default class Products extends BaseSchema {
  protected tableName = 'products'

  public async up() {
    this.schema.alterTable(this.tableName, (table) => {
      table.integer('woocommerce_product').unsigned().nullable().index()
      table.unique(['woocommerce_product'])
    })
  }

  public async down() {
    this.schema.alterTable(this.tableName, (table) => {
      table.dropUnique(['woocommerce_product'])
      table.dropColumn('woocommerce_product')
    })
  }
}

Controller:

import { schema, rules } from '@ioc:Adonis/Core/Validator'
import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'

import Product from 'App/Models/Product'

export default class ProductsController {
  private productEditValidation = {
    schema: schema.create({
      woocommerceProduct: schema.number.nullable([rules.unsigned()]),
    }),
  }

  public async update({ request, session, response, params }: HttpContextContract) {
    const productBody = await request.validate(this.productEditValidation)
    const product = await Product.findOrFail(params.id)

    // WORKING:
    await product
      .merge({
        ...productBody,
        /* DOES NOT UPDATE value in DB
        /* If i leave this key out, it does not  update when is null */
        /* DOES NOT UPDATE value in DB
        woocommerceProduct: productBody.woocommerceProduct ?? undefined */
        /* WORKING: */
        woocommerceProduct: productBody.woocommerceProduct ?? (null as unknown as undefined),
      })
      .save()

    session.flash('success', '¡El producto fue actualizado con éxito!')
    response.redirect().back()
  }
}

View:

@section('main')
  <form
    class="box"
    action="{{
      route(
        'ProductsController.update',
        { id: product.id }, {
          qs: {
            _method: 'PUT'
          }
        }
      )
    }}"
    method="POST"
  >
    {{ csrfField() }}

    <div class="field">
      <label for="woocommerceProduct" class="label">WooCommerceId</label>
      <div class="control">
        <input
          id="woocommerceProduct"
          type="number"
          class="input"
          name="woocommerceProduct"
          value="{{product.woocommerceProduct}}"
        />
      </div>
    </div>

    <div class="field is-grouped is-grouped-right">
      <p class="control">
        <button class="button is-primary" type="submit">
          <span class="icon-text">
            <span class="icon">
              <ion-icon name="save"></ion-icon>
            </span>
            <span>Guardar</span>
          </span>
        </button>
      </p>
    </div>
  </form>
@end

[RomainLanz]

Hey @leocabeza! 👋🏻

Could you share the code of your model?

[leocabeza]

@RomainLanz sure, here it is:

app/models/Product:

import { BaseModel, column } from '@ioc:Adonis/Lucid/Orm'

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

  @column()
  public woocommerceProduct: number
}

[thetutlage]

The value will be set to null when you pass null as the value. Also, your model should accept null values. The column type should be

@public woocommerceProduct: number | null