Sequelize-where子句中的子查询 [英] Sequelize - subquery in where clause

问题描述

我在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:

1. 如果在某个时间点Sequelize决定实施子查询-语法已准备就绪.
2. 再次使用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子句中的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！