Model delete while joining other tables generates wrong query

[holoyan]

Package version

^20.2.0

Describe the bug

DB: postgres

this is how my ORM query looks like

  reverseModelPermissionQuery(conditions: ModelPermissionsQuery) {

    const { modelId, modelType, permissionSlugs } = conditions
    const q = ModelPermission.query()
      .leftJoin(Permission.table + ' as p', 'p.id', '=', ModelPermission.table + '.permission_id')
      .join(ModelRole.table + ' as mr', (joinQuery) => {
        joinQuery.onVal('mr.model_type', modelType).onVal('mr.model_id', modelId)
      })
      .where((subQuery) => {
        subQuery
          .where((query) => {
            query
              .where(ModelPermission.table + '.model_type', modelType)
              .where(ModelPermission.table + '.model_id', modelId)
          })
          .orWhere((query) => {
            query
              .whereRaw('mr.role_id=' + ModelPermission.table + '.model_id')
              .where(ModelPermission.table + '.model_type', 'roles')
          })
      })

    if (permissionSlugs.length) {
      q.whereIn('p.slug', permissionSlugs)
    }

    return q
  }

// then

  emitter.on('db:query', function (query) {
    console.log(query)
  })

await this.service.reverseModelPermissionQuery({
      modelType: 'users',
      modelId: 1,
      permissionSlugs: ['edit'],
}).delete()

this is what sql query is generated

delete
from "model_permissions" using "permissions" as "p","model_roles" as "mr"
where (("model_permissions"."model_type" = ? and "model_permissions"."model_id" = ?) or
       (mr.role_id = model_permissions.model_id and "model_permissions"."model_type" = ?))
  and "p"."slug" in (?)
  and "p"."id" = "model_permissions"."permission_id"
  and "mr"."model_type" = "users"   --error here, "users" should be 'users' 
  and "mr"."model_id" = 1

bindings: [ 'users', 1, 'roles', 'edit' ],

error - column "users" does not exist

correct query I gues should be something like this

delete
from "model_permissions" using "permissions" as "p","model_roles" as "mr"
where (("model_permissions"."model_type" = ? and "model_permissions"."model_id" = ?) or
       (mr.role_id = model_permissions.model_id and "model_permissions"."model_type" = ?))
  and "p"."slug" in (?)
  and "p"."id" = "model_permissions"."permission_id"
  and "mr"."model_type" = ?
  and "mr"."model_id" = ?

bindings: [ 'users', 1, 'roles', 'edit' , 'users', 1],

---

debugger result

[ info ] watching file system for changes...
{
  duration: [ 0, 2163146 ],
  connection: 'postgres',
  inTransaction: false,
  model: 'ModelPermission',
  __knexUid: '__knexUid1',
  __knexTxId: undefined,
  method: 'del',
  options: {},
  timeout: false,
  cancelOnTimeout: false,
  bindings: [ 'users', 1, 'roles', 'edit' ],
  __knexQueryUid: 'EAzOJRN-60D4Rv25Veyfw',
  sql: 'delete from "model_permissions" using "permissions" as "p","model_roles" as "mr" where (("model_permissions"."model_type" = ? and "model_permissions"."model_id" = ?) or (mr.role_id=model_permissions.model_id and "model_permissions"."model_type" = ?)) and "p"."slug" in (?) and "p"."id" = "model_permissions"."permission_id" and "mr"."model_type" = "users" and "mr"."model_id" = 1',
  returning: undefined,
  error: error: delete from "model_permissions" using "permissions" as "p","model_roles" as "mr" where (("model_permissions"."model_type" = $1 and "model_permissions"."model_id" = $2) or (mr.role_id=model_permissions.model_id and "model_permissions"."model_type" = $3)) and "p"."slug" in ($4) and "p"."id" = "model_permissions"."permission_id" and "mr"."model_type" = "users" and "mr"."model_id" = 1 - column "users" does not exist
      at Parser.parseErrorMessage (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/parser.ts:369:69)
      at Parser.handlePacket (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/parser.ts:188:21)
      at Parser.parse (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/parser.ts:103:30)
      at Socket.<anonymous> (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/index.ts:7:48)
      at Socket.emit (node:events:519:28)
      at addChunk (node:internal/streams/readable:559:12)
      at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
      at Readable.push (node:internal/streams/readable:390:5)
      at TCP.onStreamRead (node:internal/stream_base_commons:190:23) {
    length: 106,
    severity: 'ERROR',
    code: '42703',
    detail: undefined,
    hint: undefined,
    position: '353',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3638',
    routine: 'errorMissingColumn'
  }
}
[08:54:10.173] ERROR (40052): delete from "model_permissions" using "permissions" as "p","model_roles" as "mr" where (("model_permissions"."model_type" = $1 and "model_permissions"."model_id" = $2) or (mr.role_id=model_permissions.model_id and "model_permissions"."model_type" = $3)) and "p"."slug" in ($4) and "p"."id" = "model_permissions"."permission_id" and "mr"."model_type" = "users" and "mr"."model_id" = 1 - column "users" does not exist
    request_id: "oqhwymvidqckb9xxwbstjjjn"
    x-request-id: "oqhwymvidqckb9xxwbstjjjn"
    err: {
      "type": "DatabaseError",
      "message": "delete from \"model_permissions\" using \"permissions\" as \"p\",\"model_roles\" as \"mr\" where ((\"model_permissions\".\"model_type\" = $1 and \"model_permissions\".\"model_id\" = $2) or (mr.role_id=model_permissions.model_id and \"model_permissions\".\"model_type\" = $3)) and \"p\".\"slug\" in ($4) and \"p\".\"id\" = \"model_permissions\".\"permission_id\" and \"mr\".\"model_type\" = \"users\" and \"mr\".\"model_id\" = 1 - column \"users\" does not exist",
      "stack":
          error: delete from "model_permissions" using "permissions" as "p","model_roles" as "mr" where (("model_permissions"."model_type" = $1 and "model_permissions"."model_id" = $2) or (mr.role_id=model_permissions.model_id and "model_permissions"."model_type" = $3)) and "p"."slug" in ($4) and "p"."id" = "model_permissions"."permission_id" and "mr"."model_type" = "users" and "mr"."model_id" = 1 - column "users" does not exist
              at Parser.parseErrorMessage (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/parser.ts:369:69)
              at Parser.handlePacket (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/parser.ts:188:21)
              at Parser.parse (/<PATH>adonis/v6web/node_modules/pg-protocol/src/parser.ts:103:30)
              at Socket.<anonymous> (/<PATH>/adonis/v6web/node_modules/pg-protocol/src/index.ts:7:48)
              at Socket.emit (node:events:519:28)
              at addChunk (node:internal/streams/readable:559:12)
              at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
              at Readable.push (node:internal/streams/readable:390:5)
              at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
      "length": 106,
      "name": "error",
      "severity": "ERROR",
      "code": "42703",
      "position": "353",
      "file": "parse_relation.c",
      "line": "3638",
      "routine": "errorMissingColumn",
      "status": 500
    }

Reproduction repo

No response

[holoyan]

Anything?

[thetutlage]

The issue seems to be coming directly from Knex (I suppose), because they have recently added this onVal method. knex/knex#2746.

Can you please check the same query with Knex directly and then check if the issue persists?

[holoyan]

okay, will check and come back with the result

[holoyan]

@thetutlage issue is coming from the Knex knex/knex#2746.

 const db = knex.knex(Object.assign({}, getConfig(), { debug: false }))

  var modelType = 'users'
  var modelId = 1
  var permissionSlugs = ['delete']

  const r = await db.from('model_permissions')
    .leftJoin('permissions as p', 'p.id', '=', 'model_permissions.permission_id')
    .join('model_roles as mr', (joinQuery) => {
      joinQuery.onVal('mr.model_type', modelType).andOnVal('mr.model_id', modelId)
      // joinQuery.andOnVal('mr.model_id', modelId).andOn('mr.model_type', db.raw("'"+modelType+"'")) // solution
    })
    .where((subQuery) => {
    subQuery
      .where((query) => {
        query
          .where('model_permissions.model_type', modelType)
          .where('model_permissions.model_id', modelId)
      })
      .orWhere((query) => {
        query
          .whereRaw('mr.role_id=model_permissions.model_id')
          .where('model_permissions.model_type', 'roles')
      })
  })
    .whereIn('p.slug', permissionSlugs)
    .delete()
    // .toSQL()

this generates

delete
from "model_permissions" using "permissions" as "p","model_roles" as "mr"
where (("model_permissions"."model_type" = $1 and "model_permissions"."model_id" = $2) or
       (mr.role_id = model_permissions.model_id and "model_permissions"."model_type" = $3))
  and "p"."slug" in ($4)
  and "p"."id" = "model_permissions"."permission_id"
  and "mr"."model_type" = "users" -- error here
  and "mr"."model_id" = 1;

Error - - column "users" does not exist

to fix I used raw method

// replace 
   joinQuery.onVal('mr.model_type', modelType).andOnVal('mr.model_id', modelId)
// by
   joinQuery.andOnVal('mr.model_id', modelId).andOn('mr.model_type', db.raw("'"+modelType+"'")) // solution