Hibernate Criteria: counting the most recent entries, grouped per attribute

Question

This is a knot I find quite hard to untie.

My requirement is to use the Criteria API.

I have a Team class defined as follows:

@Entity
@Table(name="TEAMS")
public class Team{

    private Set<State> states = new HashSet<State>();

    @OneToMany(cascade=CascadeType.ALL, orphanRemoval=true, mappedBy="team")
    public Set<State> getStates() {
        return states;
    }
}

A Team has a list of States because it transits through different states during its lifecycle. So the State class is defined as follows.

@Entity
@Table(name="STATES")
public class State{

    private Team team;
    private StateType type;
    private Date date;

    @ManyToOne
    @JoinColumn(name="TEAM_ID")
    public Team getTeam() {
        return team;
    }

    @Column(name="STATE_TYPE")
    public StateType getType() {
        return type;
    }

    @Temporal(TemporalType.DATE)
    @Column(name="DATE")
    public Date getDate() {
        return date;
    }
}

Now, what I want is to count the number of teams which are currently in a given state. The current state is the one with the most recent date value.

I tried with the following statement, but with no luck.

public Long getStateTypeNumber(StateType type)
{
    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(State.class);
    criteria.add(Restrictions.eq("type", type));
    criteria.addOrder(Order.desc("date"));
    criteria.setProjection(Projections.distinct(Projections.property("team")));
    criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
    criteria.setProjection(Projections.rowCount());
    return (Long) criteria.uniqueResult();
}

can anyone provide some help? Thanks.

Answer 1

I write in the following the solution I found for this problem. First, I have to retrieve a view of my State table containing the rows of the most recent states grouped by team, then I have to add restrictions for the type. The trick here is to generate a subquery with a DetachedCriteria to be used inside a Criteria (thanks to @Jan for pointing it out).

public Long getStateTypeNumber(StateType type)
{   
    DetachedCriteria maxDateQuery = DetachedCriteria.forClass(State.class);     
    maxDateQuery.setProjection(Projections.projectionList().
            add(Projections.max("date")).
            add(Projections.groupProperty("team")));

    Session session = sessionFactory.getCurrentSession();
    Criteria criteria = session.createCriteria(State.class);
    criteria.add(Subqueries.propertiesIn(new String[] {"date", "team"}, maxDateQuery));
    criteria.add(Restrictions.eq("type", type));
    criteria.setProjection(Projections.rowCount());
    return (Long) criteria.uniqueResult();      
}