将子查询序列化为字段 [英] sequelize subquery as field
问题描述
我正在尝试通过 sequelized 生成这样的查询:
I'm trying to get such query to be generated by 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
子句,因为我有很多字段要选择,而且我不想将它们全部分组(我认为它效率不高,不是它?)
I'm trying to avoid GROUP BY
clause, as I have a lot of fields to select and I don't want to group by all them (I think it's not efficient, isn't it?)
我已经尝试了几种通过 sequelize 实现的方法,包括 {include: ...}
和 {attributes: [[...]]}
,但没有任何运气.
I've tried several ways for making it happen with sequelize, include {include: ...}
and {attributes: [[...]]}
, but without any luck.
有什么想法吗?或者我应该使用一个大的 GROUP BY
子句并让所有常规"字段进行分组?
Any ideas? or maybe should I use one big GROUP BY
clause and let all the "regular" fields to be grouped-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 的扩展:
不幸的是,目前无法查询直通模型/连接表.
Querying on the through model/join table is not possible currently unfortuneatly.
您的问题也与这个问题有关,这里的问题有点像为每个用户查询关联表".
Your question is also tangentially related to this one, where the question there was sort of a "for each user query associated table".
查看测试代码 对于 include
子句,我没有看到任何组选项,这是此功能缺乏证据.
Looking at the test code for the include
clause I don't see any group options, which is an absence of evidence for this feature.
解决方案:
当然,您可以只传递查询raw":
Of course, you could just pass the query "raw":
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
实例中.
That will give you want you want, and wrapped into Customer
instances.
或者,您可以添加一个返回另一个承诺的实例方法:
Alternatively, you could add an instance method that returns another promise:
instanceMethods: {
getOrderSummary: function () {
return Order.findAll({
where: {
CustomerId: this.id
},
attributes: [
[sequelize.fn('SUM', sequelize.col('amount')), 'sum'],
'CustomerId'],
group: ['CustomerId']
});
}
}
实例方法版本不是很干净,但它可以正常工作,并且根据您的情况可能更合适.
The instance method version isn't very clean, but it works ok and may be more appropriate depending on your situation.
我发现的最佳解决方案是在查询的 attribute
字段中使用 SQL 文字.唯一的缺点是它似乎在选择其他属性时将石板擦干净,而 '*' 不会削减它.因此,您需要使用 Object.keys()
来解决此问题.
The best solution that I've found is to use a SQL literal in the attribute
field of a query. The only downside is that it seems to wipe the slate clean on selecting the other attributes, and a '*' doesn't cut it. So, you need to do the workaround with 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
给了我们正确的:
Still, it works a charm and you could use that for some more interesting nested SELECTs. And that findAll
gives us the correct:
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
模型,但这不起作用:>
BTW, I also tried doing it backwards and using the GROUP BY on the Order
model to select into the Customer
model, but that didn't work:
// Doesn't work
return Order.findAll({
attributes: [
[Sequelize.fn('COUNT', '*'), 'orderCount'],
'CustomerId'
],
include: [
{model: Customer, attributes: ['id']}
],
group: ['CustomerId']
});
这篇关于将子查询序列化为字段的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!