gpt4 book ai didi

node.js - 内部加入删除查询,Node JS,Knex JS

转载 作者:行者123 更新时间:2023-11-29 12:12:40 25 4
gpt4 key购买 nike

此查询有效:

  var query = knex('project_member')
.join('project', 'project_member.pm_project', '=', 'project.prj_id')
.join('users', 'project_member.pm_user', '=', 'users.us_id')
.where({
prj_number: inputs.params.prj_number
})
.select('pm_project', 'us_name', 'us_alias');

query.toString() 给出:

select "pm_project", "us_name", "us_alias" from "project_member" inner join "project" on "project_member"."pm_project" = "project"."prj_id" inner join "users" on "project_member"."pm_user" = "users"."us_id" where "prj_number" = '4026305'

这个查询不起作用:

  var query = knex('project_member')
.join('project', 'project_member.pm_project', '=', 'project.prj_id')
.where({
prj_number: inputs.params.prj_number,
pm_user: inputs.params.pm_user
})
.del();

query.toString() 给出:

delete from "project_member" where "prj_number" = '4026305' and "pm_user" = '1'

无法在删除查询中使用连接?您是否必须单独查询才能加入他们?

最佳答案

删除连接似乎无法正常工作(可能是由于各种数据库的删除连接语法非常不同),但您应该能够将查询重写为简单的 IN 查询;

DELETE FROM project_member
WHERE pm_user = <pm user>
AND pm_project IN (
SELECT prj_id FROM project WHERE prj_number = <prj_number>
)

...应该由(未经测试的)生成;

knex('project_member')
.where('pm_user', inputs.params.pm_user)
.whereIn('pm_project', function() {
this.select('prj_id')
.from('project')
.where('prj_number', inputs.params.prj_number);
})
.del()

...并且永远记得在您的数据库上运行来自 Internet 上随机人员的具有潜在破坏性的 SQL 之前备份您的数据 ;-)

关于node.js - 内部加入删除查询,Node JS,Knex JS,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/24549095/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com