javascript - Sequelize - 无法绑定多部分标识符 XXX
问题描述
我正在尝试在 Sequelize with SQL Server 中使用 order 选项运行查询。我已经阅读了 SO 中的一些示例,但是在 Sequelize 上运行查询时我没有找到解决方案。
let order = [["winner","new_apv","asc"]];
const include = [
{
model: Item_Supplier,
as: "itemSupplier",
attributes: ["id", "supplierOrderId", "cost"],
include: [
{
model: Supplier,
as: "supplier"
}
]
},
{
model: Winner,
as: "winner",
attributes: ["supplierId", "new_apv"],
include: [
{
model: Supplier,
as: "supplier",
attributes: ["supplierName"]
}
]
}
];
await Item.findAll({include,order})
这是错误信息
无法绑定多部分标识符“winner.new_apv”。
这是 Sequelize 生成的 SQL Server 查询:
SELECT [item].*, [itemSupplier].[id] AS
[itemSupplier.id], [itemSupplier].[supplierOrderId] AS
[itemSupplier.supplierOrderId], [itemSupplier].[cost] AS
[itemSupplier.cost], [itemSupplier->supplier].[id] AS
[itemSupplier.supplier.id], [itemSupplier->supplier].[supplierName] AS
[itemSupplier.supplier.supplierName], [itemSupplier->supplier].[duns] AS
[itemSupplier.supplier.duns]
FROM (SELECT [item].[id], [item].[item_price], [item].[common_code], [item].[uom], [item].[usage_per_item], [item].[apv],
[item].[impac_commodity], [item].[mfgname], [item].[mtr_grp_desc], [item].[description], [item].[comments], [item].[renewed_2019],
[item].[currency], [item].[contractId], [item].[mtrId], [item].[allocationId], [winner].[id] AS [winner.id],
[winner].[supplierId]
AS [winner.supplierId], [winner].[new_apv]
AS [winner.new_apv], [winner->supplier].[id]
AS [winner.supplier.id], [winner->supplier].[supplierName]
AS [winner.supplier.supplierName] FROM [items] AS [item]
INNER JOIN [winners] AS [winner]
ON [item].[id] = [winner].[itemId]
AND [winner].[deletedAt] IS NULL
INNER JOIN [suppliers] AS [winner->supplier]
ON [winner].[supplierId] = [winner->supplier].[id]
WHERE ([item].[deletedAt] IS NULL AND ([item].[contractId] = 4 AND [item].[renewed_2019] LIKE N'YES%'))
ORDER BY [item].[id] OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY) AS [item] LEFT OUTER JOIN [item_suppliers] AS [itemSupplier]
ON [item].[id] = [itemSupplier].[itemId] AND ([itemSupplier].[deletedAt] IS NULL) LEFT OUTER JOIN [suppliers]
AS [itemSupplier->supplier] ON [itemSupplier].[supplierId] = [itemSupplier->supplier].[id] ORDER BY [winner].[new_apv] ASC;
解决方案
使用 Sequelize 文字并在文字本身中添加单引号就可以了。
order = [[Sequelize.literal('"winner.new_apv"'), desc ? "DESC" : "ASC"]];
推荐阅读
- python-3.x - Python Pandas sum() 两个值之间的差异
- java - 当多个超类型上存在覆盖的方法时,Spring如何处理注释?
- asp.net - ASP.NET 隐式本地化未针对用户控件触发
- azure - GitHub 自托管运行器可以访问单独虚拟网络后面的 Azure 资源
- hp-alm - HPALM - 导出项目列表内容
- google-sheets - 移动单元格时谷歌表保护失败
- c++ - 在 C++ 中创建数独求解器时打印错误
- java - 如何从超类 Set 中过滤子类对象并将它们添加到子类 List
- python - 计算机与函数发生器之间的 TCPIP 通信错误
- javascript - 为什么我无法安装 socket.io