时间戳

首页 > 解决方案 > Sequelize 列 Table.createdAt 不存在

node.js - Sequelize 列 Table.createdAt 不存在

问题描述

我有这两张表：

const AdminUser = sequelize.define('AdminUser', {
    adminUserId: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4, 
      unique: true, 
      allowNull: false,
      primaryKey: true
    },
    adminUsername: {
      type: 'citext',
      unique: true,
      allowNull: false
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false
    },
    role: {
      type: DataTypes.ENUM,
      values: ['super-admin', 'non-super-admin']  
    },
    active: {
      type: DataTypes.BOOLEAN
    }
  })

AdminUser.belongsToMany(MsTeam, {
      through: 'AdminUserTeam',
      foreignKey: {
        name: 'adminUserId',
        allowNull: false
      }
    })

和

const MsTeam = sequelize.define('MsTeam', {
    teamId: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4, 
      unique: true, 
      allowNull: false,
      primaryKey: true
    },
    msTeamId: {
      type: DataTypes.STRING,
      unique: true,
      allowNull: false
    },
    msTeamName: {
      type: DataTypes.STRING,
      allowNull: false
    },
    active: {
      type: DataTypes.BOOLEAN
    }
  })

MsTeam.belongsToMany(models.AdminUser, {
      through: 'AdminUserTeam',
      foreignKey: {
        name: 'teamId',
        allowNull: false
      }
    })

他们通过“AdminUserTeam”建立：m 关系。启用时间戳。

但是一个简单的查询，例如，

// Include team data
  const includeMsTeam = {
    model: MsTeam,
    through: {
      attributes: []
    },
    attributes: ['teamId', 'msTeamName']
  }
const users = await AdminUser.findAll({
    attributes: ['adminUserId', 'adminUsername'],
    limit: 10,
    offset: 10,
    order: [['createdAt', 'DESC']],
    include: [includeMsTeam]
  })

因错误而失败，

{
    "error": "column AdminUser.createdAt does not exist"
}

奇怪的是，如果我删除该include字段，查询会成功

对此的解决方案是禁用此 SO answer中提到的时间戳。但是如果我想保留时间戳怎么办？如何使用关联按“createdAt”排序？

编辑：添加 SQL 查询

Executing (default): SELECT "AdminUser".*, "MsTeams"."teamId" AS "MsTeams.teamId", "MsTeams"."msTeamName" AS "MsTeams.msTeamName", "MsTeams->AdminUserTeam"."createdAt" AS "MsTeams.AdminUserTeam.createdAt", "MsTeams->AdminUserTeam"."updatedAt" AS "MsTeams.AdminUserTeam.updatedAt", "MsTeams->AdminUserTeam"."adminUserId" AS "MsTeams.AdminUserTeam.adminUserId", "MsTeams->AdminUserTeam"."teamId" AS "MsTeams.AdminUserTeam.teamId" FROM (SELECT "AdminUser"."adminUserId", "AdminUser"."adminUsername" FROM "AdminUsers" AS "AdminUser" WHERE "AdminUser"."role" = 'non-super-admin' AND ( SELECT "AdminUserTeam"."adminUserId" FROM "AdminUserTeam" AS "AdminUserTeam" INNER JOIN "MsTeams" AS "MsTeam" ON "AdminUserTeam"."teamId" = "MsTeam"."teamId" WHERE ("AdminUser"."adminUserId" = "AdminUserTeam"."adminUserId") LIMIT 1 ) IS NOT NULL ORDER BY "AdminUser"."createdAt" DESC LIMIT 20 OFFSET 0) AS "AdminUser" LEFT OUTER JOIN ( "AdminUserTeam" AS "MsTeams->AdminUserTeam" INNER JOIN "MsTeams" AS "MsTeams" ON "MsTeams"."teamId" = "MsTeams->AdminUserTeam"."teamId") ON "AdminUser"."adminUserId" = "MsTeams->AdminUserTeam"."adminUserId" ORDER BY "AdminUser"."createdAt" DESC;

错误发生在，

ORDER BY "AdminUser"."createdAt" DESC

标签： node.jssequelize.js

解决方案

sequelize 似乎生成了很多子查询，并且createdAt对 top-most 不可见ORDER BY，请尝试添加subQuery: false到 options 对象。如果这没有帮助，添加createdAt到attributes应该工作。

推荐阅读