使用 NOT IN 子句和嵌套的 SELECT 对查询进行 Sequelize [英] Sequelize query with NOT IN clause and nested SELECT

问题描述

我有一个 SQL 查询，我想使用 sequelize 查询语法表达它.

I have an SQL query which I would like to express using the sequelize query syntax.

数据库方案

• 用户:(id、用户名、...)
• 群组:(id、名称、...)
• User_Groups:(id、#groupId、#userId)

• Users: (id, username, ...)
• Groups: (id, name, ...)
• User_Groups: (id, #groupId, #userId)

查询说明

检索尚未属于特定组的所有用户.

Retrieve all users that are not yet part of a particular group.

原始 SQL 查询(工作)

SELECT User.id, User.email FROM Users as User 
  WHERE User.id NOT IN (
    SELECT User_Group.userId FROM User_Groups as User_Group 
    WHERE User_Group.groupId = ${groupId}
  )

我被屏蔽的地方

我尝试使用 sequelize include 语句模拟连接，但我的大脑被困在转换嵌套的 SELECT 查询以及 NOT IN 运算符...

I tried to simulate a join using the sequelize include statement, but my brain is stuck transforming the nested SELECT query as well as the NOT IN operator...

推荐答案

这是不可能的表示子查询，而不是在 where 子句中使用 Sequelize.literal ，如下所示:

It's not possible to represent sub-queries other than using Sequelize.literal in the where clause like this:

const users = await database.User.findAll({
  where: Sequelize.where(Sequelize.literal(`(User.id NOT IN (
    SELECT User_Group.userId FROM User_Groups as User_Group 
    WHERE User_Group.groupId = '${groupId}'
  ))`)
}

或者您可以将 Op.notIn 与 Sequelize.literal

这篇关于使用 NOT IN 子句和嵌套的 SELECT 对查询进行 Sequelize的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！