代码之家  ›  专栏  ›  技术社区  ›  PirateApp

为什么sequelize不能使用select where in子句正确生成此查询?

sequelize.js postgresql
  •  0
  • PirateApp  · 技术社区  · 4 年前
    • 我有两个SQL查询,我正试图将它们转换为这些查询的sequelize表示

    我要转换为sequelize版本的第一个SQL查询 

    SELECT 
  user_id, 
  a.account_id, 
  email, 
  email_verified, 
  user_id IN (
    SELECT 
      user_id 
    FROM 
      user_roles 
    WHERE 
      role_id IN (
        SELECT 
          role_id 
        FROM 
          roles 
        WHERE 
          role_name = 'admin'
      )
  ) AS is_admin, 
  username, 
  picture_url, 
  social_account_id, 
  created_at, 
  updated_at 
FROM 
  accounts a 
  LEFT JOIN user_accounts ua ON a.account_id = ua.account_id 
WHERE 
  a.account_id = $1

    第一个查询:续集版本 

    function getByPk(accountId) {
    const subquery = `
        SELECT 
          user_id 
        FROM 
          user_roles 
        WHERE 
          role_id IN (
            SELECT 
              role_id 
            FROM 
              roles 
            WHERE 
              role_name = 'admin'
          )
    `;
    return Account.findByPk(accountId, {
        attributes: {
            exclude: ['password'],
            include: [
                [sequelize.literal(`"Users".user_id IN (${subquery})`), 'isAdmin']
            ]
        },
        include: [{
            model: User,
            attributes: ['userId', 'enabled'],
            through: {
                attributes: []
            }
        }]
    });
}

    上面的sequelize版本在执行时运行良好,并生成以下SQL查询

    第一个查询生成以下SQL 

    SELECT 
  "Account"."account_id" AS "accountId", 
  "Account"."email", 
  "Account"."email_verified" AS "emailVerified", 
  "Account"."is_primary" AS "isPrimary", 
  "Account"."username", 
  "Account"."picture_url" AS "pictureUrl", 
  "Account"."social_account_id" AS "socialAccountId", 
  "Account"."created_at" AS "createdAt", 
  "Account"."updated_at" AS "updatedAt", 
  "Account"."authentication_type_id" AS "authenticationTypeId", 
  "Users".user_id IN (
    SELECT 
      user_id 
    FROM 
      user_roles 
    WHERE 
      role_id IN (
        SELECT 
          role_id 
        FROM 
          roles 
        WHERE 
          role_name = 'admin'
      )
  ) AS "isAdmin", 
  "Users"."user_id" AS "Users.userId", 
  "Users"."enabled" AS "Users.enabled" 
FROM 
  "accounts" AS "Account" 
  LEFT OUTER JOIN (
    "user_accounts" AS "Users->UserAccount" 
    INNER JOIN "users" AS "Users" ON "Users"."user_id" = "Users->UserAccount"."user_id"
  ) ON "Account"."account_id" = "Users->UserAccount"."account_id" 
WHERE 
  "Account"."account_id" = 1

    我的第二个原始SQL查询使用findOne而不是findByPk,如下所示 

    SELECT 
  user_id, 
  a.account_id, 
  email, 
  email_verified, 
  user_id IN (
    SELECT 
      user_id 
    FROM 
      user_roles 
    WHERE 
      role_id IN (
        SELECT 
          role_id 
        FROM 
          roles 
        WHERE 
          role_name = 'admin'
      )
  ) AS is_admin, 
  nickname, 
  picture_url, 
  social_account_id, 
  created_at, 
  updated_at 
FROM 
  accounts a 
  LEFT JOIN user_accounts ua ON a.account_id = ua.account_id 
WHERE 
  authentication_type_id = (
    SELECT 
      authentication_type_id 
    FROM 
      authentication_types 
    WHERE 
      authentication_type_name = $1
  ) 
  AND email = $2

    现在,我的sequelize版本的查询抛出了一个错误,如下所示

    第二个SQL查询:sequelize版本 (各方面与第一个相同) 

    function getByEmail(email) {
    const subquery = `
            SELECT 
              user_id 
            FROM 
              user_roles 
            WHERE 
              role_id IN (
                SELECT 
                  role_id 
                FROM 
                  roles 
                WHERE 
                  role_name = 'admin'
              )
`;
    return Account.findOne({
        attributes: {
            exclude: ['password'],
            include: [
                [sequelize.literal(`"Users".user_id IN (${subquery})`), 'isAdmin']
            ]
        },
        include: [{
            model: User,
            attributes: ['userId', 'enabled'],
            through: {
                attributes: []
            }
        }],
        where: {
            email
        }
    });
}

    当我执行上述函数时,它抛出以下错误 

    Error
    at Query.run (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/sequelize/dist/lib/dialects/postgres/query.js:50:25)
    at /Users/vivekramesh/Desktop/code/imagineapp4/node_modules/sequelize/dist/lib/sequelize.js:313:28
    at processTicksAndRejections (internal/process/task_queues.js:95:5)
    at async PostgresQueryInterface.select (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/sequelize/dist/lib/dialects/abstract/query-interface.js:396:12)
    at async Function.findAll (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/sequelize/dist/lib/model.js:1102:21)
    at async Function.findOne (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/sequelize/dist/lib/model.js:1196:12) {
  name: 'SequelizeDatabaseError',
  parent: error: missing FROM-clause entry for table "Users"
      at Parser.parseErrorMessage (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (events.js:400:28)
      at Socket.emit (domain.js:537:15)
      at addChunk (internal/streams/readable.js:293:12)
      at readableAddChunk (internal/streams/readable.js:267:9)
      at Socket.Readable.push (internal/streams/readable.js:206:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
    length: 117,
    severity: 'ERROR',
    code: '42P01',
    detail: undefined,
    hint: undefined,
    position: '507',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3545',
    routine: 'errorMissingRTE',
    sql: `SELECT "Account".*, "Users"."user_id" AS "Users.userId", "Users"."enabled" AS "Users.enabled" FROM (SELECT "Account"."account_id" AS "accountId", "Account"."email", "Account"."email_verified" AS "emailVerified", "Account"."is_primary" AS "isPrimary", "Account"."username", "Account"."picture_url" AS "pictureUrl", "Account"."social_account_id" AS "socialAccountId", "Account"."created_at" AS "createdAt", "Account"."updated_at" AS "updatedAt", "Account"."authentication_type_id" AS "authenticationTypeId", "Users".user_id IN (SELECT user_id FROM user_roles WHERE role_id IN (SELECT role_id FROM roles WHERE role_name = 'admin')) AS "isAdmin" FROM "accounts" AS "Account" WHERE "Account"."email" = 'vivek.9891@gmail.com' LIMIT 1) AS "Account" LEFT OUTER JOIN ( "user_accounts" AS "Users->UserAccount" INNER JOIN "users" AS "Users" ON "Users"."user_id" = "Users->UserAccount"."user_id") ON "Account"."accountId" = "Users->UserAccount"."account_id";`,
    parameters: undefined
  },
  original: error: missing FROM-clause entry for table "Users"
      at Parser.parseErrorMessage (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/parser.js:287:98)
      at Parser.handlePacket (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/parser.js:126:29)
      at Parser.parse (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/parser.js:39:38)
      at Socket.<anonymous> (/Users/vivekramesh/Desktop/code/imagineapp4/node_modules/pg-protocol/dist/index.js:11:42)
      at Socket.emit (events.js:400:28)
      at Socket.emit (domain.js:537:15)
      at addChunk (internal/streams/readable.js:293:12)
      at readableAddChunk (internal/streams/readable.js:267:9)
      at Socket.Readable.push (internal/streams/readable.js:206:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:188:23) {
    length: 117,
    severity: 'ERROR',
    code: '42P01',
    detail: undefined,
    hint: undefined,
    position: '507',
    internalPosition: undefined,
    internalQuery: undefined,
    where: undefined,
    schema: undefined,
    table: undefined,
    column: undefined,
    dataType: undefined,
    constraint: undefined,
    file: 'parse_relation.c',
    line: '3545',
    routine: 'errorMissingRTE',
    sql: `SELECT "Account".*, "Users"."user_id" AS "Users.userId", "Users"."enabled" AS "Users.enabled" FROM (SELECT "Account"."account_id" AS "accountId", "Account"."email", "Account"."email_verified" AS "emailVerified", "Account"."is_primary" AS "isPrimary", "Account"."username", "Account"."picture_url" AS "pictureUrl", "Account"."social_account_id" AS "socialAccountId", "Account"."created_at" AS "createdAt", "Account"."updated_at" AS "updatedAt", "Account"."authentication_type_id" AS "authenticationTypeId", "Users".user_id IN (SELECT user_id FROM user_roles WHERE role_id IN (SELECT role_id FROM roles WHERE role_name = 'admin')) AS "isAdmin" FROM "accounts" AS "Account" WHERE "Account"."email" = 'vivek.9891@gmail.com' LIMIT 1) AS "Account" LEFT OUTER JOIN ( "user_accounts" AS "Users->UserAccount" INNER JOIN "users" AS "Users" ON "Users"."user_id" = "Users->UserAccount"."user_id") ON "Account"."accountId" = "Users->UserAccount"."account_id";`,
    parameters: undefined
  },
  sql: `SELECT "Account".*, "Users"."user_id" AS "Users.userId", "Users"."enabled" AS "Users.enabled" FROM (SELECT "Account"."account_id" AS "accountId", "Account"."email", "Account"."email_verified" AS "emailVerified", "Account"."is_primary" AS "isPrimary", "Account"."username", "Account"."picture_url" AS "pictureUrl", "Account"."social_account_id" AS "socialAccountId", "Account"."created_at" AS "createdAt", "Account"."updated_at" AS "updatedAt", "Account"."authentication_type_id" AS "authenticationTypeId", "Users".user_id IN (SELECT user_id FROM user_roles WHERE role_id IN (SELECT role_id FROM roles WHERE role_name = 'admin')) AS "isAdmin" FROM "accounts" AS "Account" WHERE "Account"."email" = 'vivek.9891@gmail.com' LIMIT 1) AS "Account" LEFT OUTER JOIN ( "user_accounts" AS "Users->UserAccount" INNER JOIN "users" AS "Users" ON "Users"."user_id" = "Users->UserAccount"."user_id") ON "Account"."accountId" = "Users->UserAccount"."account_id";`,
  parameters: {}
}

    进一步检查,sequelize似乎没有正确生成此查询,下面是从第二个查询生成的原始SQL的样子 

    SELECT 
  "Account".*, 
  "Users"."user_id" AS "Users.userId", 
  "Users"."enabled" AS "Users.enabled" 
FROM 
  (
    SELECT 
      "Account"."account_id" AS "accountId", 
      "Account"."email", 
      "Account"."email_verified" AS "emailVerified", 
      "Account"."is_primary" AS "isPrimary", 
      "Account"."username", 
      "Account"."picture_url" AS "pictureUrl", 
      "Account"."social_account_id" AS "socialAccountId", 
      "Account"."created_at" AS "createdAt", 
      "Account"."updated_at" AS "updatedAt", 
      "Account"."authentication_type_id" AS "authenticationTypeId", 
      "Users".user_id IN (
        SELECT 
          user_id 
        FROM 
          user_roles 
        WHERE 
          role_id IN (
            SELECT 
              role_id 
            FROM 
              roles 
            WHERE 
              role_name = 'admin'
          )
      ) AS "isAdmin" 
    FROM 
      "accounts" AS "Account" 
    WHERE 
      "Account"."email" = 'vivek.9891@gmail.com' 
    LIMIT 
      1
  ) AS "Account" 
  LEFT OUTER JOIN (
    "user_accounts" AS "Users->UserAccount" 
    INNER JOIN "users" AS "Users" ON "Users"."user_id" = "Users->UserAccount"."user_id"
  ) ON "Account"."accountId" = "Users->UserAccount"."account_id

    有人能告诉我为什么生成的版本在这两个方面有很大的不同,以及如何运行第二个版本? 

    User has a belongsToMany with Account through UserAccount
Account has a belongsToMany with User through UserAccount
    0 回复  |  直到 4 年前
    推荐文章
    Johnny T  ·  基于当前值的SQL合并表[重复]
    1 年前
    Kallydi  ·  jdbc无法连接到docker中的postgresql数据库
    1 年前
    dietzi96  ·  Pandas DataFrame.to_sql随机和静默地失败,没有错误消息
    1 年前
    Peter Schofield  ·  类型转换Postgresql
    1 年前
    Barış Uşaklı  ·  如何在ON冲突更新中使用CTE中的值
    1 年前
    Kevin Smeeks  ·  Pyspark JDBC分区读取
    1 年前
    Micah Armantrout  ·  如何使用sql脚本将数据插入到代码优先的数据库中?
    1 年前
    Vanortton  ·  使用Docker开始应用程序开发还是在开发过程中配置Docker?[关闭]
    1 年前
    Andrus  ·  如何在sql中查找第二个匹配项
    1 年前
    MWood  ·  Postgres jsonpath用于引用单例元素或元素数组
    1 年前
    关于   移动版
    代码之家 - 一站式码农服务社区
    沪ICP备11025650号