Background
We run tests on several points on a regular basis that give test results (either fail or fail). One test can be applied to several elements, and one element can have several different tests performed on it.
What we need to do is make a list of all the item / test combinations that are currently failing and the number of failures since the last pass for each item / test combination.
Due to limitations in the system we are working with, we must use the Hibernate API, not HQL. The data set is potentially very large.
Problem
I came up with the following approach:
Get the last passing result from each test element, and then the inner join, which uses the testResults table to get test result identifiers that don't work from 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 to create this inner join using criteria API? Subqueries only work in the WHERE clause, so they are not suitable.
The following code creates a criterion that gets the date of the last test for each combination of tests / elements, but I need TestResult to which this date belongs.
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 greatly appreciated