无法使用 Sequelize libarary 连接三个具有 Sum 聚合函数的表

Question

我正在尝试连接三个表Organization_entries和用户和user_invoices，我在sql中有一个这样的查询

select users.id ,users.name,users.email, users.mobile, sum(user_invoices.due_amount) , 
organization_entries.id, organization_entries.created_at  
from users 
INNER JOIN user_invoices ON users.id = user_invoices.customer_id 
INNER JOIN organization_entries on users.id = organization_entries.user_id 
GROUP BY users.id, organization_entries.id 
ORDER BY organization_entries.created_at DESC;

此查询工作正常，并给了我想要的所需结果，但是当我尝试使用 Sequelize libaray 转换此查询时，我无法理解如何在 Sequelize 库中转换此查询。

我在续集中尝试过这样的事情

db.users
.findAll({
where: condition,
duplicating: false,
attributes: [
'id',
'name',
'email',
'mobile',
'mobileAlternate',
'image',
'gender',
'dob',
'status',
[
db.sequelize.fn(
'SUM',
db.sequelize.col('userInvoices.due_amount')
),
'dueAmount'
],
],
include: [
{
model: db.userInvoices,
attributes: ['dueAmount'],
},
{
model: db.organizationEntries,
attributes: ['id', 'fromDate', 'status', 'createdAt'],
required: true,
where: playerCondition,
duplicating: false
},
],
group: ['users.id', 'organizationEntries.id'],
order: [['organizationEntries', 'createdAt', 'DESC']],
offset: pageCondition.startRecord,
limit: pageCondition.endRecord
})

但它给了我这个错误
"'字段列表'中的未知列'userInvoices.due_amount'"，

这是生成的查询

"users从(选择users.id，users。name，users。email，users。mobile，users。mobile_alternate如mobileAlternate，users。image，users.gender， 总和(userInvoices.due_amount) 如amount、users。dob，users.status从usersusers哪里(users.deleted_at> '2018-02-27 15：22：30' 或users.deleted_at为空)按users分组。id，organizationEntries。id限制 0， 20000) 作为左外连接usersuser_invoices作为users上的userInvoices。id=userInvoices.customer_id和(userInvoices.deleted_at> '2018-02-27 15：22：30' 或userInvoices.deleted_at为空)

任何人都可以给我一些提示如何在 Sequelize 中编写此查询。

Answer 1

更改

db.sequelize.col('userInvoices.due_amount')

自：

db.sequelize.col('user_invoices.due_amount')