Bug 150 : @Formula ... use in where clause and using with @Transient
Priority 
High
Reported Version 
 
Logged By 
Rob
Status 
Fixed
Fixed Version 
2.1.0
Assigned To 
 
Product 
Ebean - core
Duplicate Of 
 
Created 
11/09/2009
Updated 
11/09/2009
Type 
Bug
 
Attachments 
No attachments

With @Formula the expression was not being correctly translated if it was used in a where clause.

Also, we want to be able to use @Transient with @Formula - that is, if the @Formula is somewhat expensive, the @Transient will exclude it from queries UNLESS it is explicitly included in the query (aka explicitly included in query.select(...); or query.join(..,..);

 
Rob 12 Sep 03:29
Example
public Order(){
                
        }

        
        @Id
    Integer id;

        /**
         * Derived total amount from the order details. Needs to be explicitly included in query as Transient.
         * Removing the Transient would mean by default it would be included in a order query.
         * <p>
         * NOTE: The join clause for totalAmount and totalItems is the same. If your query includes both 
         * totalAmount and totalItems only the one join is added to the query.
         * </p>
         */
        @Transient
        @Formula(
                select="_b${ta}.total_amount",
                join="join (select order_id, count(*) as total_items, 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")
        Double totalAmount;

        /**
         * Derived total item count from the order details. Needs to be explicitly included in query as Transient.
         */
        @Transient
        @Formula(
                select="_b${ta}.total_items",
                join="join (select order_id, count(*) as total_items, 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")
        Integer totalItems;

Rob 12 Sep 03:30
And to query...
List<Order> l2 = Ebean.find(Order.class)
        .select("id, totalAmount")
        .where()
                .eq("status", Order.Status.NEW)
                .gt("totalItems", 1)
                .gt("totalAmount", 10)
        .findList();

Rob 12 Sep 03:33
The Transient

The @Formula means its never included in insert,update,delete.

The @Transient on the totalAmount and totalItems fields indicates that these should not be included a query by default.

However, you can specify explicitly to include the totalAmount, totalItems or both in your query as is done above.

Rob 12 Sep 03:34
Resulting SQL:
select o.id c0, _bo.total_amount 
from o_order o
join (select order_id, count(*) as total_items, 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 = ?  and _bo.total_items > ?  and _bo.total_amount > ?

Rob 12 Sep 03:35
This bug...

This bug is to allow the formula fields to be used in the where clause and to also allow @Transient to be used with @Formula.

woResponse

Upload a file