Wrong queries fired while using preload with null relationships

[Marian0]

Hey guys,

I have two models: Expense and PayementMethod.

• One Expense can have one payment method
• One Expense can have no payment method (null)
• Payment Methods could be related to none or many Expenses

Given those business rules, I created these two models:

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

 // ......

  @hasMany(() => Expense)
  public expenses: HasMany<typeof Expense>
}

export default class Expense extends compose(BaseModel, Filterable) {
  
  @column({ isPrimary: true })
  public id: number

//.....

  @column()
  public payment_method_id?: string

  @belongsTo(() => PaymentMethod, { foreignKey: "payment_method_id" })
  public paymentMethod: BelongsTo<typeof PaymentMethod>
}

This is working fine however, while using preload with expenses having null payment methods I see this query in the debugger which doesn't make sense:

    const paginatedData = await Expense.filter(validatedInput)
      .where('user_id', currentUser.id)
      .preload("category")
      .preload("paymentMethod")
      .orderBy(order_by, order_direction === "ASC" ? "asc" : "desc")
      .paginate(page, per_page)

"pg" PaymentMethod (2.47 ms) SELECT * FROM "payment_methods" WHERE 1 = ? [ 0 ]

It seems like there's some bug (or wrong relationship definition on my end) that tries to get related entities even if the foreing key is null resulting on inefficient DB usage because of sending incorrect queries.

Package version

18.4.2

Node.js and npm version

Node: v18.14.2
Npm: 9.5.0

[thetutlage]

Hello, can you please create a fresh project of this setup I can use to reproduce the issue?

[Marian0]

hey @thetutlage - I've just pushed this sample project so you can easily reproduce the bug:

https://github.com/Marian0/adonis-lucid-playground

I wrote some lines in the readme so it should be easy to follow

Let me know if you have any question, happy to help 👍