[ebean] Reporting Bean / "RawSql" ... example 2:

[Rob Bygrave]

So in this case we are using the OrderAggregate as our 'root' again

... but partially populating the order object and the order.customer object.



    public void test() {
       
        ResetBasicData.reset();
               
        String sql
            = " select order_id, o.status, c.id, c.name, sum(d.order_qty*d.unit_price) "
            + " from o_order o"
            + " join o_customer c on c.id = o.kcustomer_id "
            + " join o_order_detail d on d.order_id = o.id "
            + " group by order_id, o.status ";
       
        RawSql rawSql =
            RawSqlBuilder
                .parse(sql)
                .columnMapping("order_id",  "order.id")
                .columnMapping("o.status",  "order.status")
                .columnMapping("c.id",      "order.customer.id")
                .columnMapping("c.name",    "order.customer.name")
                .columnMapping("sum(d.order_qty*d.unit_price)", "totalAmount")
                .create();

       
        Query<OrderAggregate> query = Ebean.find(OrderAggregate.class);
            query.setRawSql(rawSql)       
            .fetch("order.details", new FetchConfig().query())
            .where().gt("order.id", 1)
            .having().gt("totalAmount", 20);
       
        List<OrderAggregate> list = query.findList();
        assertNotNull(list);

        for (OrderAggregate oa : list) {
            Double totalAmount = oa.getTotalAmount();
            Order order = oa.getOrder();
            Integer id = order.getId();
            Status status = order.getStatus();
            System.out.println("Order: "+id+" "+status+" total:"+totalAmount);
           
            Customer c = order.getCustomer();
            System.out.println("   -> customer: "+c.getId()+" "+c.getName());
           
            order.getOrderDate();
           
        }
       
       
    }