Newbie Question: Group By in Ebean

[Mayank Jain]

Hello,

Here is a completely newbie question we are struggling with. Any help or pointer to a doc/ tutorial will be greatly appreciated.

Consider that I have an entity V

id
date
s
amount


I want to do the equivalent of

mysql> select sum(s*amount) as total from tbl_v where date < some_date;

what are my options with ebean. How do I do it?

Best,

Mayank

[Mayank Jain]

oops! missed the group by part:

V will have:
id
date
l_id
s
amount

and the query will be:
mysql> select l_id, sum(s*amount) as total from tbl_v where date < some_date group by l_id;

Thnx,

[Rob Bygrave]

Hmmm. 

You have a probably 3 options...

1. Use raw sql ... Ebean.createSqlQuery()
2. Use a "report" type entity bean... aka an entity bean based on raw SQL
3. Use @Formula



1. RAW SQL
-------------------------------

        String sql = "select order_id, sum(order_qty*unit_price) as total_amount from o_order_detail  where order_qty > :minQty  group by order_id";
        List<SqlRow> sqlRows = Ebean.createSqlQuery(sql)
            .setParameter("minQty", 1)
            .findList();
       
        // just getting the first row of the list
        SqlRow sqlRow = sqlRows.get(0);
       
        Integer id = sqlRow.getInteger("order_id");
        Double amount = sqlRow.getDouble("total_amount");

[Rob Bygrave]

2. "REPORT" type entity bean
-----------------------------------------------

@Entity
@Sql(select = {
  @SqlSelect(
    name="default",
    query="select order_id, sum(order_qty*unit_price) as total_amount from o_order_detail group by order_id"
  )
})
public class OrderReport {

    Integer orderId;
   
    Double totalAmount;

    public Integer getOrderId() {
        return orderId;
    }

    public void setOrderId(Integer orderId) {
        this.orderId = orderId;
    }

    public Double getTotalAmount() {
        return totalAmount;
    }

    public void setTotalAmount(Double totalAmount) {
        this.totalAmount = totalAmount;
    }
   
}


....


     List<OrderReport> list = Ebean.find(OrderReport.class)
            .where().gt("order_qty", 1)
            .having().gt("totalAmount", 20.50)
            .findList();

// results in

select order_id, sum(order_qty*unit_price) as total_amount
from o_order_detail 

where order_qty > ? 
group by order_id 
having total_amount > ? 


Notes:
- The "order_qty" is not a known property (on the OrderReport) so it is goes into sql as is
- The "totalAmount" is a known property... so gets converted
- The DB column names are mapping to bean properties automatically via naming convention
- You can use a HAVING clauses
- The query name "default" is special  ...

[Rob Bygrave]

3. USE @FORMULA
----------------------------------------------

Say I have Order -< OrderDetail ... and we want the order to have a "total amount" for it's order details.

// there is a @Formula example in the userguide pdf ... which hopefully explains using the @Formula with a subquery, join to a view and finally joining to another sql statement (which is what is shown below)


    @Formula(
        select="_b${ta}.total_amount",
        join="join (select order_id, sum(order_qty*unit_price) as total_amount from o_order_detail group by order_id) as _b${ta} on _b${ta}.order_id = ${ta}.id")
    String totalAmount;


List<Order> list = Ebean.find(Order.class)
    .select("id, totalAmount")
    .where().eq("status", Order.Status.NEW)
    .findList();

which generates sql ...

select o.id c0, _bo.total_amount
from o_order o
join (select order_id, sum(order_qty*unit_price) as total_amount from o_order_detail group by order_id) as _bo on _bo.order_id = o.id 
where o.status = ?


If you have time you want to compare the query plans of the subquery approach vs the join (select ...)


NOTE:  I've just found a 2 bugs around this...
--------------
1. You want to be able to use @Transient on the @Formula .. so its only included in the query when you explicit include it in the ORM select.

2. There is a bug using the totalAmount property in the where clause ...