CODEWITHSUNDEEP
hibernatejoinsubqueryaggregate-functions
spikeheap
spikeheap

Reputation: 3887

Hibernate Criteria restrict by aggregation

Background

We carry out tests on multiple items on a regular basis, which produce test results (either pass or fail). A single test can be applied to multiple items, and a single item can have multiple different tests carried out on it.

What we need to do is produce a list of all item/test combinations which are currently failing the test, and the number of failures since the last passfor each item/test combination.

Because of limitations with the system we're working with, the we must use the Hibernate Criteria API, not HQL. The data set is potentially very large.

Problem

I have come up with the following approach:

Get the latest passing result from each test item, then inner join that with the testResults table to get ids of TestResults which fail since the last pass

SELECT 
  test.id, 
  item.id, 
  COUNT(item.id) 
FROM testResults 
INNER JOIN (SELECT item.id, test.id, MAX(testDate) FROM testResults GROUP BY item.id, test.id) AS lastPass
ON (lastPass.item.id = item.id AND lastPass.test.id = test.id)
GROUP BY
  test.id
  item.id

How can I create this inner join using the Criteria API? Subqueries only appear to work in the WHERE section, so aren't appropriate.

The following code creates a criteria which gets the date of the latest test for each test/item combination, but I need the TestResult which that date belongs to. 

DetachedCriteria maxDate = new DetachedCriteria(TestResult.class, "maxDate");
maxDate.setProjection(Projections.projectionList()
    .add(Projections.max("testDate"))
    .add(Projections.groupProperty("test"))
    .add(Projections.groupProperty("item"))
    );
maxDate.add(Property.forName("maxDate.test").eqProperty("mainQuery.test"));
maxDate.add(Property.forName("maxDate.item").eqProperty("mainQuery.item"));

Any help would be much appreciated,

Upvotes: 5

Views: 3857

Answers (1)

Stijn Geukens
Stijn Geukens

Reputation: 15628

I think you're pretty close, you don't need to 'Property.forName' for referencing a main query from the subquery:

    Criteria mainQuery = getSession().createCriteria(TestResult.class, "mainQuery");

    DetachedCriteria maxDate = new DetachedCriteria(TestResult.class, "maxDate");
    maxDate.setProjection(Projections.projectionList()
            .add(Projections.max("testDate"))
            .add(Projections.groupProperty("test"))
            .add(Projections.groupProperty("item")));
    maxDate.add(Restrictions.eqProperty("maxDate.test", "mainQuery.test"));
    maxDate.add(Restrictions.eqProperty("maxDate.item", "mainQuery.item"));

    mainQuery.setProjection(Projections.projectionList()
            .add(Projections.groupProperty("test"))
            .add(Projections.groupProperty("item"))
            .add(Projections.rowCount()));

    result = mainQuery.list();

    //use some AliasToBeanResultTransformer to convert the projected result into beans

Try if this works...

Upvotes: 3

Related Questions