gom2ment

How to get the sum of numeric column in Ent


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)

Solution

  • 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]