Where子句在' OR '语句中使用包含关系

Where clause using included relation in `OR` statement

本文关键字：关系 包含 OR 子句 Where 语句      更新时间：2023-09-26

我正在使用Sequelize将一个项目从MySQL移动到Postgres，有一件事已经失败了。这似乎是由于'User'没有包含在任何地方。

编辑:错误信息是'missing FROM-clause entry for table users'.

sequelize的代码是:

List.model.findOne({
  where: {
    id: listId,
    $or: [
      { open: true },
      ['Users.id = ?', [userId]], // <-- the line I think is breaking this.
    ],
  },
  include: [{
    model: db.models.User
  }],
});

我认为这是由于User没有包括在FROM条款中。但是，我不确定如何使Sequelize做正确的事情。

Sequelize生成的查询是:

SELECT "List"."id",
   "List"."name",
   "List"."image",
   "List"."slug",
   "List"."open",
   "List"."password",
   "List"."createdAt",
   "List"."updatedAt",
   "List"."OwnerId",
   "Users"."id" AS "Users.id",
   "Users"."firstName" AS "Users.firstName",
   "Users"."lastName" AS "Users.lastName",
   "Users"."email" AS "Users.email",
   "Users"."password" AS "Users.password",
   "Users"."image" AS "Users.image",
   "Users"."facebookId" AS "Users.facebookId",
   "Users"."googleId" AS "Users.googleId",
   "Users"."createdAt" AS "Users.createdAt",
   "Users"."updatedAt" AS "Users.updatedAt",
   "Users.UserList"."createdAt" AS "Users.UserList.createdAt",
   "Users.UserList"."updatedAt" AS "Users.UserList.updatedAt",
   "Users.UserList"."ListId" AS "Users.UserList.ListId",
   "Users.UserList"."UserId" AS "Users.UserList.UserId"
FROM "Lists" AS "List"
LEFT OUTER JOIN ("UserList" AS "Users.UserList"
             INNER JOIN "Users" AS "Users" ON "Users"."id" =     "Users.UserList"."UserId") ON "List"."id" = "Users.UserList"."ListId"
WHERE "List"."id" = 13
  AND ("List"."open" = TRUE
   OR (Users.id = 1234));

尝试像"Users"."id"一样用双引号包装Users.id，因为在PostgreSQL中所有未引号的标识符(表名，字段)将被折叠成小写，PostgreSQL是大小写敏感的:https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

对标识符加引号也使其区分大小写，而未加引号的名称总是折叠成小写。例如，标识符FOO, FOO和"FOO"被PostgreSQL认为是相同的，但"FOO"answers"FOO"与这三者不同，彼此也不同。(在PostgreSQL中将未加引号的名字折叠成小写与SQL标准不兼容，SQL标准规定未加引号的名字应该折叠成大写。因此，根据标准，foo应该等同于"foo"而不是"foo"。如果你想编写可移植的应用程序，建议你总是引用一个特定的名称，或者永远不要引用它。

相关文章：