Error when syncing a many-to-many relation that uses onQuery

[waffleau]

Running into a problem when trying to use model.relation('relation').sync([1, 2]) when the relationship is defined as many-to-many and has an onQuery. This is because when using relation Lucid doesn't doesn't load the data in the "relation" table, it only loads the pivot table.

Given these two models (and a join table):

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

  @manyToMany(() => Role, {
    pivotTable: "user_roles",
    onQuery(query) {
      query.whereNull("deletedAt");
    },
  })
  public roles: ManyToMany<typeof Role>;
}

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

  @column.dateTime()
  public deletedAt: DateTime | null = null;
}

This will cause an error:

const user = await User.create()
const role = await Role.create()
await user.related('roles').sync([role.id])

The error:

error: select "role_id" from "user_roles" where ("deleted_at" is null and "role_id" in ($1)) and ("user_id" = $2) - column "deleted_at" does not exist

This is because the roles table isn't joined when calling model.relation(...).

Package version

Lucid v18.4.2

Node.js and npm version

node v18.7.0
npm v8.15.0

Sample Code (to reproduce the issue)

https://github.com/waffleau/adonis-many-to-many-bug

1. Checkout the above repo
2. Create/configure DB env
3. npm install
4. npm test

[FaisalHussain95]

Hi @waffleau

It looks like you are using onQuery, as of now the documentation is probably not teaching us mush about this but what i have concluded is that onQuery will run for any types of request that is made on your Model User

onQuery should be used on specific type of request that need preloading your Role model.

I tried a small fix on your code and it looks like ok.

  @manyToMany(() => Role, {
    pivotTable: "user_roles",
    onQuery(query) {
      if (query.isRelatedPreloadQuery) {
        query.whereNull("deletedAt");
      }
    },
  })
  public roles: ManyToMany<typeof Role>;

As this is not written is the documentation this solution seems a little alieny.

I would love to read what you have come up with.

Regards