我正在尝试让这样的查询由 sequlized 生成:
SELECT
"Customers"."id",
(SELECT SUM("Orders"."amount") FROM "Orders"
WHERE "Orders"."CustomerId" = "Customers"."id") AS "totalAmount",
"Customer"."lastName" AS "Customer.lastName",
"Customer"."firstName" AS "Customer.firstName"
FROM "Customers" AS "Customer";
我试图避免GROUP BY
子句,因为我有很多字段可供选择,我不想按所有字段分组(我认为这效率不高,不是吗?
我已经尝试了几种方法来实现续集,包括{include: ...}
和{attributes: [[...]]}
,但没有任何运气。
有什么想法吗? 或者也许我应该使用一个大的GROUP BY
子句并让所有"常规"字段分组?
你最好的选择是:
return Customer.findAll({
attributes: Object.keys(Customer.attributes).concat([
[sequelize.literal('(SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id")'), 'totalAmount']
])
});
<小时 />这看起来像是问题 #1869 的扩展:
不幸的是,目前无法查询通过模型/连接表。
您的问题也与此问题密切相关,其中的问题有点像"针对每个用户查询关联的表"。
查看 include
子句的测试代码,我没有看到任何组选项,这是缺乏此功能的证据。
解决 方案:
当然,您可以只传递查询"原始":
return sequelize.query(
'SELECT *, (SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id") AS "totalAmount" FROM "Customers" AS "Customer";',
Customer,
{raw: false}
);
这将提供您想要的,并包装到Customer
实例中。
或者,您可以添加一个返回另一个 promise 的实例方法:
instanceMethods: {
getOrderSummary: function () {
return Order.findAll({
where: {
CustomerId: this.id
},
attributes: [
[sequelize.fn('SUM', sequelize.col('amount')), 'sum'],
'CustomerId'],
group: ['CustomerId']
});
}
}
实例方法版本不是很干净,但它工作正常,根据您的情况可能更合适。
我找到的最佳解决方案是在查询的attribute
字段中使用 SQL 文本。唯一的缺点是,在选择其他属性时,它似乎将石板擦干净,并且"*"不会削减它。因此,您需要使用Object.keys()
执行解决方法。
return Customer.findAll({
attributes: Object.keys(Customer.attributes).concat([
[sequelize.literal('(SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id")'), 'totalAmount']
])
});
尽管如此,它仍然具有魅力,您可以将其用于一些更有趣的嵌套 SELECT。这个findAll
给了我们正确的答案:
Executing (default): SELECT "id", "firstName", "lastName", "createdAt", "updatedAt", (SELECT SUM("Orders"."amount") FROM "Orders" WHERE "Orders"."CustomerId" = "Customer"."id") AS "totalAmount" FROM "Customers" AS "Customer";
{ id: 1,
firstName: 'Test',
lastName: 'Testerson',
createdAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
updatedAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
totalAmount: 15 }
{ id: 2,
firstName: 'Invisible',
lastName: 'Hand',
createdAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
updatedAt: Wed Feb 04 2015 08:05:42 GMT-0500 (EST),
totalAmount: 99 }
顺便说一句,我也尝试向后执行此操作,并在Order
模型上使用 GROUP BY 选择进入Customer
模型,但这不起作用:
// Doesn't work
return Order.findAll({
attributes: [
[Sequelize.fn('COUNT', '*'), 'orderCount'],
'CustomerId'
],
include: [
{model: Customer, attributes: ['id']}
],
group: ['CustomerId']
});
在 Sequelize 4 中,您可以使用 attributes.include
语法添加额外的属性 http://docs.sequelizejs.com/manual/tutorial/querying.html
return Customer.findAll({
attributes: {
include: [
[sequelize.literal('(SELECT SUM("Orders"."amount") FROM "Orders"
WHERE "Orders"."CustomerId" = "Customer"."id")'), 'totalAmount']
]
}
});