Hibernate Criteria 通过聚合进行限制

发布于 2024-10-24 11:36:49 字数 1204 浏览 2 评论 0原文

背景

我们定期对多个项目进行测试,并产生测试结果(通过或失败)。单个测试可以应用于多个项目,并且单个项目可以对其执行多个不同的测试。

我们需要做的是生成当前未通过测试的所有项目/测试组合的列表,以及自上次通过以来每个项目/测试组合的失败次数。

由于我们正在使用的系统的限制,我们必须使用 Hibernate Criteria API,而不是 HQL。数据集可能非常大。

问题

我想出了以下方法:

从每个测试项获取最新的通过结果,然后将其与 testResults 表进行内部联接,以获取自上次通过以来失败的 TestResults 的 id

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

如何使用 Criteria API 创建此内部联接?子查询似乎只在 WHERE 部分中工作,因此不合适。

以下代码创建一个条件,获取每个测试/项目组合的最新测试的日期,但我需要该日期所属的 TestResult。

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"));

任何帮助将不胜感激,

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,

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

梦里南柯 2024-10-31 11:36:49

我认为你已经非常接近了,你不需要“Property.forName”来从子查询引用主查询:

    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

尝试一下这是否有效...

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...

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文