续集查找和计数所有查询选择我尚未指定要选择的字段

Question

以下是我正在处理的代码：

Transaction.findAndCountAll({
where: {
operation: 'NA_WITHDRAWAL',
status: 'PENDING',
},
attributes: [
[Sequelize.fn('sum', Sequelize.col('amount')), 'sum'],
[Sequelize.fn('min', Sequelize.col('Transaction.createdAt')), 'oldestPendingWithdrawal'],
'withdrawalNetwork',
'Wallet->Coin.id',
'Wallet->Coin.decimals',
'Wallet->User.autoWithdrawal',
],
group: ['withdrawalNetwork', 'Wallet->User.autoWithdrawal', 'Wallet->User.id', 'Wallet->Coin.decimals', 'Wallet->Coin.id'],
include: [{
model: Wallet,
required: true,
attributes: [],
include: [{
model: Coin,
required: true,
attributes: ['id', 'decimals'],
}, {
model: User,
attributes: ['autoWithdrawal'],
required: true,
}],
}],
})

这个查询确实有效，但它迫使我在group数组中包含Wallet->User.id，这确实使我的结果出错。它不允许我在没有按Wallet->User.id分组的情况下运行，这对于sequelize尝试选择Wallet->User.id甚至我来说都是有意义的，因为我没有在属性部分指定我需要选择用户id，这是我感到困惑的地方。

下面是由上面的代码生成的查询(注意注释行，这是不需要的-有了这些注释行，查询运行良好，我得到了所需的输出(：

SELECT sum("amount") AS "sum", 
min("Transaction"."createdAt") AS "oldestPendingWithdrawal", 
"Transaction"."withdrawalNetwork", 
"Wallet->Coin"."id", 
"Wallet->Coin"."decimals", 
"Wallet->User"."autoWithdrawal", 
"Wallet->Coin"."id" AS "Wallet.Coin.id", 
"Wallet->Coin"."decimals" AS "Wallet.Coin.decimals", 
-- "Wallet->User"."id" AS "Wallet.User.id", 
"Wallet->User"."autoWithdrawal" AS "Wallet.User.autoWithdrawal" 
FROM "Transactions" AS "Transaction" 
INNER JOIN "Wallets" AS "Wallet" ON "Transaction"."WalletId" = "Wallet"."id" 
INNER JOIN "Coins" AS "Wallet->Coin" ON "Wallet"."CoinId" = "Wallet->Coin"."id" 
INNER JOIN "Users" AS "Wallet->User" ON "Wallet"."UserId" = "Wallet->User"."id" 
WHERE "Transaction"."operation" = 'NA_WITHDRAWAL' AND "Transaction"."status" = 'PENDING' 
GROUP BY 
"withdrawalNetwork", 
"Wallet->User"."autoWithdrawal", 
"Wallet->Coin"."decimals", 
-- "Wallet->User"."id",
"Wallet->Coin"."id";

因此，我试图为上面的查询编写代码，基本上是为了排除用户id的选择和分组。

非常感谢您的贡献！

Answer 1

Sequelize查询包含联接表，并且根据包含表中的字段进行分组将有局限性，因为Sequeliz的内置查询生成器不直接支持此类SQL查询。

我建议对同一使用原始查询

const result = await sequelize.query(
'You're Find and Join Query and then grouping by',
'GROUP BY <fieldName>', 
);

这将有助于嵌套包含和按查询分组