node.js - 在 Sequelize 查找方法的 where 条件中使用列别名时出错

Question

简要说明:我正在尝试迁移我们的应用程序以使用 Sequelize，并且希望依靠列别名的使用，因为我们正在处理的实际列名称相当长且令人费解。不幸的是，在 Sequelize findAll()、findOne() 等方法中构造 where 条件时，我似乎无法使用别名属性。
我的(简化)设置如下:

// package.json
{
  ...
  "dependencies": {
    "pg": "^7.18.1",
    "pg-hstore": "^2.3.3",
    "sequelize": "^5.21.4"
  }
}

// sequelize.js

const Sequelize = require('sequelize')

const options = {
    ...
    databaseVersion: '8.0.2', // RedShift Postgres version
    dialect: 'postgres',
    dialectOptions: {
        ssl: {
            require: true,
            rejectUnauthorized: true
        }
    }
    define: {
        timestamps: false,
        freezeTableName: true
    }
}

const sequelize = new Sequelize(<database>, <user>, <pass>, options)

module.exports = sequelize

// MyTable.model.js

const { DataTypes } = require('sequelize')
const sequelize = require('./sequelize')

const schema = 'my_schema'
const tableName = 'my_table'
const modelName = 'MyTable'

const attributes = {
    my_table_key: {
        type: DataTypes.CHAR(32)
    },
    key: {
        field: 'my_table_key',
        type: DataTypes.CHAR(32)
    },
    value: {
        field: 'my_table_value',
        type: DataTypes.STRING
    }
}

const options = {
    schema
    tableName
}

const model = sequelize.define(modelName, attributes, options)

module.exports = model

执行如下查询就可以了:

MyTable.findOne({ attributes: ['key'] })
>> Executing (default): SELECT "my_table_key" AS "key" FROM "my_schema"."my_table" AS "my_table" LIMIT 1;

MyTable.findOne({ attributes: ['key'], where: { key_column: '123' } })
>> Executing (default): SELECT "my_table_key" AS "key" FROM "my_schema"."my_table" AS "my_table" WHERE ("my_table"."my_table_key" = '123') LIMIT 1;

但是，一旦我尝试使用重命名/别名属性，就会遇到错误:

MyTable.findOne({ attributes: ['key'], where: { key: '123' } })

Unhandled rejection SequelizeDatabaseError: column mytable.key does not exist
at Query.formatError (~/node_modules/sequelize/lib/dialects/postgres/query.js:366:16)
    at query.catch.err (~/node_modules/sequelize/lib/dialects/postgres/query.js:72:18)
    at bound (domain.js:301:14)
    at runBound (domain.js:314:12)
    at tryCatcher (~/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (~/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (~/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (~/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (~/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (~/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (~/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (~/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues (~/node_modules/bluebird/js/release/async.js:15:14)
    at runCallback (timers.js:810:20)
    at tryOnImmediate (timers.js:768:5)
    at processImmediate [as _immediateCallback] (timers.js:745:5)

我在定义模型时尝试了 modelName 和 tableName 变量(例如，只保留原始表名而没有自定义模型名)，但遇到相同的错误。有谁知道为 where 参数使用别名列的正确方法(如果有的话)？

谢谢!

Answer 1

我从未见过以这种方式完成的别名(在模型定义期间)，我不建议您这样做。
我建议您保留相同的键/列，并且只有在获取时，才应该使用 attributes 键更改列名以供以后使用:

MyTable.findOne({ attributes: [['LONG_COLUMN_NAME1', 'key1'], ['LONG_COLUMN_NAME2', 'key2']] }) // This will select LONG_COLUMN_NAME1 as key1 and LONG_COLUMN_NAME2 as key2.