聚合

Aggregation

The Aggregate option allows adding one or more aggregation functions.

  1. package main
  2. import (
  3. "context"
  4. "<project>/ent"
  5. "<project>/ent/payment"
  6. "<project>/ent/pet"
  7. )
  8. func Do(ctx context.Context, client *ent.Client) {
  9. // Aggregate one field.
  10. sum, err := client.Payment.Query().
  11. Aggregate(
  12. ent.Sum(payment.Amount),
  13. ).
  14. Int(ctx)
  15. // Aggregate multiple fields.
  16. var v []struct {
  17. Sum, Min, Max, Count int
  18. }
  19. err := client.Pet.Query().
  20. Aggregate(
  21. ent.Sum(pet.FieldAge),
  22. ent.Min(pet.FieldAge),
  23. ent.Max(pet.FieldAge),
  24. ent.Count(),
  25. ).
  26. Scan(ctx, &v)
  27. }

Group By

Group by name and age fields of all users, and sum their total age.

  1. package main
  2. import (
  3. "context"
  4. "<project>/ent"
  5. "<project>/ent/user"
  6. )
  7. func Do(ctx context.Context, client *ent.Client) {
  8. var v []struct {
  9. Name string `json:"name"`
  10. Age int `json:"age"`
  11. Sum int `json:"sum"`
  12. Count int `json:"count"`
  13. }
  14. err := client.User.Query().
  15. GroupBy(user.FieldName, user.FieldAge).
  16. Aggregate(ent.Count(), ent.Sum(user.FieldAge)).
  17. Scan(ctx, &v)
  18. }

Group by one field.

  1. package main
  2. import (
  3. "context"
  4. "<project>/ent"
  5. "<project>/ent/user"
  6. )
  7. func Do(ctx context.Context, client *ent.Client) {
  8. names, err := client.User.
  9. Query().
  10. GroupBy(user.FieldName).
  11. Strings(ctx)
  12. }

Group By Edge

Custom aggregation functions can be useful if you want to write your own storage-specific logic.

The following shows how to group by the id and the name of all users and calculate the average age of their pets.

  1. package main
  2. import (
  3. "context"
  4. "log"
  5. "<project>/ent"
  6. "<project>/ent/pet"
  7. "<project>/ent/user"
  8. )
  9. func Do(ctx context.Context, client *ent.Client) {
  10. var users []struct {
  11. ID int
  12. Name string
  13. Average float64
  14. }
  15. err := client.User.Query().
  16. GroupBy(user.FieldID, user.FieldName).
  17. Aggregate(func(s *sql.Selector) string {
  18. t := sql.Table(pet.Table)
  19. s.Join(t).On(s.C(user.FieldID), t.C(pet.OwnerColumn))
  20. return sql.As(sql.Avg(t.C(pet.FieldAge)), "average")
  21. }).
  22. Scan(ctx, &users)
  23. }

Having + Group By

Custom SQL modifiers can be useful if you want to control all query parts. The following shows how to retrieve the oldest users for each role.

  1. package main
  2. import (
  3. "context"
  4. "log"
  5. "entgo.io/ent/dialect/sql"
  6. "<project>/ent"
  7. "<project>/ent/user"
  8. )
  9. func Do(ctx context.Context, client *ent.Client) {
  10. var users []struct {
  11. Id Int
  12. Age Int
  13. Role string
  14. }
  15. err := client.User.Query().
  16. Modify(func(s *sql.Selector) {
  17. s.GroupBy(user.Role)
  18. s.Having(
  19. sql.EQ(
  20. user.FieldAge,
  21. sql.Raw(sql.Max(user.FieldAge)),
  22. ),
  23. )
  24. }).
  25. ScanX(ctx, &users)
  26. }

Note: The sql.Raw is crucial to have. It tells the predicate that sql.Max is not an argument.

The above code essentially generates the following SQL query:

  1. SELECT * FROM user GROUP BY user.role HAVING user.age = MAX(user.age)