英文:
How to get the sum of numeric column in Ent
问题
我有两个表：products和baskets，它们之间有M2M关系，表名为basket_products。我想要获取特定篮子相关的products的总price和数量，但是我又遇到了困难。如何修复以下代码以获取结果？注意：我正在使用Ent框架。
err = client.Basket.
Query().
Where(basket.ID(2)).
WithProducts().
QueryProducts().
GroupBy("price").
Aggregate(func(selector *sql.Selector) string {
return sql.As(sql.Sum("price"), "price")
}).Scan(ctx, &r)
if err != nil {
log.Println(err)
}
log.Println(r)
英文:
I have two tables: products and baskets, which have the M2M relation and the table name is basket_products. I am trying to get the specific basket-related products total price and quantity, but I am getting stuck again. How can I fix the following code to get the result? note: I am using the Ent framework
err = client.Basket.
Query().
Where(basket.ID(2)).
WithProducts().
QueryProducts().
GroupBy("price").
Aggregate(func(selector *sql.Selector) string {
return sql.As(sql.Sum("price"), "price")
}).Scan(ctx, &r)
if err != nil {
log.Println(err)
}
log.Println(r)
答案1
得分: 2
你是否尝试将GroupBy("price")更改为GroupBy(Basket.FieldPrice)，即通过生成的字段名称引用字段。示例中的Group By Edge应该类似。https://entgo.io/docs/aggregate/
英文:
Did you try changing GroupBy("price") to GroupBy(Basket.FieldPrice)
i.e. referencing the fields by generated field name.
The example Group By Edge should be similar https://entgo.io/docs/aggregate/
答案2
得分: 2
Aggregate和GroupBy函数是朋友。聚合 | Ent
我根据以下给出的代码进行了修改，并得到了一个非常优化的SQL查询，感谢Ent团队。
err := client.Basket.
Query().
Where(basketEntity.ID(ID)).
GroupBy(basketEntity.FieldID).
Aggregate(func(s *sql.Selector) string {
t := sql.Table(product.Table)
joinT := sql.Table(basketEntity.ProductsTable)
s.Join(joinT).
On(s.C(basketEntity.FieldID), joinT.C(basketEntity.ProductsPrimaryKey[0]))
s.Join(t).
On(t.C(product.FieldID), joinT.C(basketEntity.ProductsPrimaryKey[1]))
return sql.As(sql.Sum(t.C(product.FieldPrice)), "price")
}).
Aggregate(repository.Count()).
Scan(ctx, &report)
生成的SQL：
SELECT "baskets"."id", SUM("t2"."price") AS "price", COUNT(*)
FROM "baskets"
JOIN "basket_products" AS "t1" ON "baskets"."id" = "t1"."basket_id"
JOIN "products" AS "t2" ON "t2"."id" = "t1"."product_id"
WHERE "baskets"."id" = $1
GROUP BY "baskets"."id" args=[1]
英文:
The Aggregate and GroupBy functions are the friends. Aggregation | Ent
I modified the code as given below and got a very optimized SQL query, thanks to the Ent team.
err := client.Basket.
Query().
Where(basketEntity.ID(ID)).
GroupBy(basketEntity.FieldID).
Aggregate(func(s *sql.Selector) string {
t := sql.Table(product.Table)
joinT := sql.Table(basketEntity.ProductsTable)
s.Join(joinT).
On(s.C(basketEntity.FieldID), joinT.C(basketEntity.ProductsPrimaryKey[0]))
s.Join(t).
On(t.C(product.FieldID), joinT.C(basketEntity.ProductsPrimaryKey[1]))
return sql.As(sql.Sum(t.C(product.FieldPrice)), "price")
}).
Aggregate(repository.Count()).
Scan(ctx, &report)
The generated SQL:
SELECT "baskets"."id", SUM("t2"."price") AS "price", COUNT(*)
FROM "baskets"
JOIN "basket_products" AS "t1" ON "baskets"."id" = "t1"."basket_id"
JOIN "products" AS "t2" ON "t2"."id" = "t1"."product_id"
WHERE "baskets"."id" = $1
GROUP BY "baskets"."id" args=[1]
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论