Lucid generates invalid SQL query when using a related model query

[aikrom]

I have a User and Contact model. The relationship between these models is many-to-many because Contact may have relationships with other models in the future.

User:

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

  @manyToMany(() => Contact)
  public contacts: ManyToMany<typeof Contact>
}

Contact:

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

  @column()
  public type: string

  @column()
  public value: string
}

When I run the code below to find a contact:

export default class TestController extends BaseModel {
  public async delete({ auth, bouncer, request }: HttpContextContract) {
    const contactId = request.param('contact_id')

    const contact = await auth.user!.related('contacts').query().where('id', contactId).firstOrFail()
    await contact.delete()

    return 'OK'
  }
}

I am getting an error:

Error: Error: select `contacts`.*, `contact_user`.`user_id` as `pivot_user_id`, `contact_user`.`contact_id` as `pivot_contact_id` from `contacts` inner join `contact_user` on `contacts`.`id` = `contact_user`.`contact_id` where (`id` = 1) and (`contact_user`.`user_id` = 1) limit 1 - SQLITE_ERROR: ambiguous column name: id

The contact table name does not prefixed in the SQL query. I think it's a bug, but I'm not sure. Has anyone come across this situation?

[WeyIin]

why not try finding the contact with the ID AND user id? Might be easier and better?
e.g.

Contact.query().where('id', contactId).andWhere('user_id', auth.user.id)

[aikrom]

With a many-to-many relationship, we can't do this because there is no user_id column in the contacts table.

[LuizCristino]

@aikrom Don't know if you resolved or moved on. But if you still have this problem, you can try this query.

export default class TestController extends BaseModel {
  public async delete({ auth, bouncer, request }: HttpContextContract) {
    const contactId = request.param('contact_id')

    const contact = await auth.user!.related('contacts').query().where('contacts.id', contactId).firstOrFail()
    await contact.delete()

    return 'OK'
  }
}

[aikrom]

Thanks, I know this approach and the problem is that you have hardcoded the table name, this can lead to errors if the table ever changes.

[LuizCristino]

Well, I tough you were trying to resolve about the ambiguous column name problem. But like you said the contacts is hardcoded, the way you can resolve is using this.

const contactTable: string = Contact.table;

const contact = await auth.user!.related(contactTable).query().where(`${contactTable}.id`, contactId).firstOrFail()