HQL: group by month

Question

I'm trying to group by month some rows using HQL, but I'm kind of new to that API and can't seem to get it to work.

Here is my code:

        Criteria query = getHibernateSession().createCriteria(SalesPerformance.class);

        // summary report is grouped by date
        query.setProjection(Projections.projectionList().add(
                Projections.groupProperty("effectiveDate"), "effectiveDate").add(
                Projections.groupProperty("primaryKey.seller", "seller").add(
                Projections.sum("totalSales"))));


        // sub-select based on seller id
        query.add(Property.forName("primaryKey.seller.id").eq(sellerId)).setFetchMode(
                "primaryKey.seller", FetchMode.SELECT);

        query.add(Property.forName("primaryKey.effectiveDate").le(new Date()));
        query.add(Property.forName("primaryKey.effectiveDate").ge(DateUtils.truncate(new Date(), Calendar.MONTH)));
        query.addOrder(Order.desc("primaryKey.effectiveDate"));

        return query.list();

My problem with this query is that it's going to return one row per day when I need one row per month because of Projections.groupProperty("effectiveDate").

I thought about using Projections.sqlGroupProjection instead of Projections.groupProperty and throw in some HQL, but the documentation and the couple examples I found didn't really help me understand how I would go about putting the right postresql statement in that method.

Anyone who knows about Postgres and HQL could give some hints here please?

Answer 1

Found the solution:

Criteria query = getHibernateSession().createCriteria(SalesPerformance.class);

    // summary report is grouped by date
            query.setProjection(Projections.projectionList().add(Projections.sqlGroupProjection("date_trunc('month', eff_dt) as eff_dt_value", "eff_dt_value", new String[] {"eff_dt_value"}, new Type[] {Hibernate.DATE})).add(
                            Projections.groupProperty("primaryKey.seller", "seller").add(
                            Projections.sum("totalSales"))));


            // sub-select based on seller id
            query.add(Property.forName("primaryKey.seller.id").eq(sellerId)).setFetchMode(
                            "primaryKey.seller", FetchMode.SELECT);

            query.add(Property.forName("primaryKey.effectiveDate").le(new Date()));
            Date beginningOfLastMonth = DateUtils.truncate(DateUtils.addMonths(new Date(), -1) , Calendar.MONTH);
            Date endOfLastMonth = DateUtils.addDays(DateUtils.truncate(new Date(), Calendar.MONTH), -1);
            query.add(Property.forName("primaryKey.effectiveDate").between(beginningOfLastMonth, endOfLastMonth));


            return query.list();

Please note that in my case I need to grab values with effectiveDate from last month.

Hopefully that will help others in the same boat! :)