lucid
lucid copied to clipboard
adonisjs
Model delete while joining other tables generates wrong query
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
The issue seems to be coming directly from Knex (I suppose), because they have recently added this onVal method. https://github.com/knex/knex/pull/2746.
Can you please check the same query with Knex directly and then check if the issue persists?
@thetutlage issue is coming from the Knex https://github.com/knex/knex/pull/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