问题描述
我在Express应用程序中使用 Sequelize .我需要生成一个在WHERE
子句中具有子查询的查询.
I'm using Sequelize in my Express app. I need to generate a query that has a subquery in the WHERE
clause.
SELECT *
FROM MyTable
WHERE id NOT IN (
SELECT fkey
FROM MyOtherTable
WHERE field1 = 1
AND field2 = 2
AND field3 = 3
)
我首先通过模型尝试了关系/关联,但无法使其正常工作.像这样:
I first tried relations/associations through my models but couldn't get it to work. Something like:
MyTable.find( {
where: {
id: {
$notIn: // <= what goes here? Can I somehow reference my include model?
}
},
include: [ {
model: MyOtherTable,
where: {
field1: 1,
field2: 2,
field3: 3
} ]
} );
然后我尝试使用Sequelize.where()
,那里没有运气.
Then I tried using Sequelize.where()
, no luck there.
然后我尝试了Sequelize.literal()
,但是我不确定是否是在Sequelize中的where子句中进行子查询的正确" 方法.
Then I tried Sequelize.literal()
and that works but not sure if it's a "proper" way of doing a subquery in a where clause in Sequelize as I'm new to it.
MyTable.find( {
where: {
id: {
$notIn: sequelize.literal(
'( SELECT fkey ' +
'FROM MyOtherTable ' +
'WHERE field1 = ' + field1 +
' AND field2 = ' + field2 +
' AND field3 = ' + field3 +
')'
}
}
} );
我也知道我可以使用Sequelize.query()
,但是我真的不知道该伸手还是literal()
马上到,因为我感觉有些东西在忽略.
I also know that I could use Sequelize.query()
but don't really know if I should reach for it or if literal()
is the right away as I feel like there's something I'm overlooking.
我真的很想知道如何使用"proper" 方式对WHERE
子句执行子查询.
I would really like to know how to perform a subquery in a WHERE
clause with Sequelize the "proper" way.
感谢您的反馈!
推荐答案
我在项目中遇到了类似的问题.我选择实现它的方式有些不同,有两个原因:
I have encountered a similar issue in my project.The way I choose to implement it is a bit different for two reasons:
- 如果在某个时间点Sequelize决定实施子查询-语法已准备就绪.
- 再次使用Sequelize保护进行SQL注入.
这是我的代码段,希望对您有所帮助.
Here is my code snippet, hope it helps.
const tempSQL = sequelize.dialect.QueryGenerator.selectQuery('MyOtherTable',{
attributes: ['fkey'],
where: {
field1: 1,
field2: 2,
field3: 3
}})
.slice(0,-1); // to remove the ';' from the end of the SQL
MyTable.find( {
where: {
id: {
$notIn: sequelize.literal('(' + tempSQL + ')'),
}
}
} );
某些人可能选择不使用tempSQL变量,而只是在find结构中构建SQL(也许使用辅助方法?)
Some people might choose to not use the tempSQL variable and simply build the SQL inside the find structure (maybe using a helper method?)
我还认为这可能是子查询扩展的基础,因为它几乎使用相同的语法.
I also think this might be the basis for a sub queries extension for sequelize as it uses the same syntax almost.
这篇关于Sequelize-where子句中的子查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!