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 👍

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[watzon]

I am having a very similar issue here, so this should definitely be reopened. It seems as though the preload mechanism for belongsTo relationships isn't executing the related query when the relation is nullable. At least that has been my experience. The workaround that I've found so far is just to not attempt to preload those relationships and instead manually load them. But obviously it would be really nice to have the preloading working correctly.