Criteria with projections not returning correct object type

joey · **Joined:** Sat Mar 29, 2008 6:02 pm **Posts:** 4

Hi there,

I'm new to Criteria and I'm having a bit of trouble trying to generate a query.

I have the following object structure where status change events for a given order are stored in a separate history table:

Order { customerId, displayableOrderId, List<OrderStatusChange>, ... }
OrderStatus { description, ... }
OrderStatusChange { order, orderStatus, statusChangedDate, ... }

I'm trying to list all orders which have had a status change within a specified time interval for a given customer; in SQL I would write:

Code:

    select o.CUSTOMER_ID, o.DISPLAYABLE_ORDER_ID, o...
      from ORDERS o
inner join ORDER_STATUS_CHANGES s on o.ORDER_ID = s.ORDER_ID
     where o.CUSTOMER_ID = :customerId
       and s.STATUS_CHANGED_DATE between :statusChangedStartDate and :statusChangedEndDate
  group by o.CUSTOMER_ID, o.DISPLAYABLE_ORDER_ID, o...
  order by max(s.STATUS_CHANGED_DATE) desc, o.DISPLAYABLE_ORDER_ID;

So far, I have the following hibernate criteria. The problem is that I'm not sure how I can get it to group by all the properties of the order class and have it return an instance of an Order. Right now, this is throwing a ClassCastException when it attempts to cast the results list to the typed Order list because, well they aren't Order instances.

Code:

DetachedCriteria q = DetachedCriteria.forClass(Order.class)
    .createAlias("orderStatusChanges", "s")
    .setProjection(projectionList()
        .add(max("s.statusChangedDate"), "maxStatusChangedDate")
        .add(groupProperty("displayableOrderId")))
    .add(eq("customerId", customerId))
    .add(between("s.statusChangedDate", statusChangedStartDate, statusChangedEndDate))
    .addOrder(desc("maxStatusChangedDate"))
    .addOrder(asc("displayableOrderId"));

result = (List<Order>)getHibernateTemplate().findByCriteria(q, firstResult, noOfResults);

Here is the SQL that hibernate generated:

Code:

    select
        *
    from
        ( select
            row_.*,
            rownum rownum_
        from
            ( /* criteria query */ select
                max(s1_.STATUS_CHANGED_DATE) as y0_,
                this_.DISPLAYABLE_ORDER_ID as y1_
            from
                ORDERS this_
            inner join
                ORDER_STATUS_CHANGES s1_
                    on this_.ORDER_ID=s1_.ORDER_ID
            where
                this_.CUSTOMER_ID=?
                and s1_.STATUS_CHANGED_DATE between ? and ?
            group by
                this_.DISPLAYABLE_ORDER_ID
            order by
                y0_ desc,
                this_.DISPLAYABLE_ORDER_ID asc ) row_
        where
            rownum <= ?
        )
    where
        rownum_ > ?

Any ideas what I'm doing wrong?

Thanks,
J

ramnath · **Posted:** Sun Mar 30, 2008 12:56 am

your query is Scalar Query (with Max Aggregate function), which return array (Object[] ). For more info look into the below link
http://www.hibernate.org/hib_docs/refer ... larqueries

joey · **Joined:** Sat Mar 29, 2008 6:02 pm **Posts:** 4

Thanks for the response.

So that makes sense why I can't cast it... but I guess this isn't really how I want the query to behave. Ideally, I would like it to return instances of the Order class just like I would if I wasn't aggregating.

The only use of the join in this specific query is to apply some filters and to use the max for ordering - none of the data in the joined table or produced by aggregation functions is actually required.

Is it possible to do this with Hibernate Criteria?

Thanks,
J

ramnath · **Posted:** Sun Mar 30, 2008 12:24 pm

If you don't want to have Object Array as return type...do change your query as below...
.add( Projections.max("s.statusChangedDate") )
or
.add( Projections.max("s.statusChangedDate"), "maxStatusChangedDate" )
or
.add( Property.forName("s.statusChangedDate").max().as("maxStatusChangedDate") )

Kindly gothrough the below link to know more about Criteria and Scalar queries..

http://www.hibernate.org/hib_docs/refer ... teria.html

Hope this helps:-)

joey · **Joined:** Sat Mar 29, 2008 6:02 pm **Posts:** 4

Thanks again.

I have looked through the docs but I'm just not seeing it :( The only way to add a field which is an aggregations is using projections - but that always results in a scalar.

Code:

DetachedCriteria q = DetachedCriteria.forClass(Order.class);
q.createAlias("orderStatusChanges", "s");
q.setProjection(forName("s.statusChangedDate").max().as("maxStatusChangedDate"));
q.add(eq("customerId", customerId));
q.add(between("s.statusChangedDate", statusChangedStartDate, statusChangedEndDate));
q.addOrder(desc("maxStatusChangedDate"));
q.addOrder(asc("displayableOrderId"));

result = (List<Order>)getHibernateTemplate()
    .findByCriteria(q, firstResult, noOfResults + 1);

Here is the SQL which is being generated:

Code:

            ( /* criteria query */ select
                max(s1_.STATUS_CHANGED_DATE) as y0_
            from
                ORDERS this_
            inner join
                ORDER_STATUS_CHANGES s1_
                    on this_.ORDER_ID=s1_.ORDER_ID
            where
                this_.CUSTOMER_ID=?
                and s1_.STATUS_CHANGED_DATE between ? and ?
            order by
                y0_ desc,
                this_.DISPLAYABLE_ORDER_ID asc )

Here is the SQL which I *think* I'm trying to achieve; where this_... represents all the fields from the ORDERS table:

Code:

            ( /* criteria query */ select
                this_.DISPLAYABLE_ORDER_ID,
                this_.CUSTOMER_ID,
                this_.ORDER_DATE,
                this_...
            from
                ORDERS this_
            inner join
                ORDER_STATUS_CHANGES s1_
                    on this_.ORDER_ID=s1_.ORDER_ID
            where
                this_.CUSTOMER_ID=?
                and s1_.STATUS_CHANGED_DATE between ? and ?
           group by
                this_.DISPLAYABLE_ORDER_ID,
                this_.CUSTOMER_ID,
                this_.ORDER_DATE,
                this_...
           order by
                max(s1_.STATUS_CHANGED_DATE) desc,
                this_.DISPLAYABLE_ORDER_ID asc )

Thanks,
J

joey · **Joined:** Sat Mar 29, 2008 6:02 pm **Posts:** 4

Anyone know how I can have a query sort by an aggregated field without returning the results as a scalar?

Thanks,
J