在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.