以一对多关系获取父项的最大记录 [英] Fetch max records of parent in one to many relationship

问题描述

假设有两个类，分别是ParentClass和ChildClass.父母要背着书包生孩子.
我已经尝试过.SetResultTransformer(new DistinctRootEntityResultTransformer())和distinct()来过滤掉重复项，并且在获取.SetMaxResults()时我没有在ParentClass级别得到它.

Suppose two class are there a ParentClass and a ChildClass. Parent is having a bag to have childs.
I have tried .SetResultTransformer(new DistinctRootEntityResultTransformer()) and distinct() which filters out the repeatitions and when fetching .SetMaxResults() I am not getting it in ParentClass level.

是否有什么可用于使.SetMaxResults()在ParentClass级别而不是ChildClass上工作的.我需要在父级上执行maxresults.

Is there anything which can be used to get make the .SetMaxResults() to work on ParentClass level and not on ChildClass. I need to enforce the maxresults in Parent level.

示例ParentClass有6个孩子，并且setmaxresults(6)和distinct()将导致我得到一个ParentClass，而我正在查询中查找更多5个ParentClass记录.我的标准包括3个与父记录匹配的参数和2个与子记录匹配的参数

Example ParentClass having 6 childs and setmaxresults(6) and distinct() would result me to a single ParentClass while I am looking for more 5 ParentClass records in my query. And my criteria includes 3 parameters to match with Parent record and 2 to match with Child record

推荐答案

一种解决方案是使用子查询.文档 14.11.子查询.

One solution could be to use the subquery. The documentation 14.11. Subqueries.

它将像内部选择一样工作.子查询将包含带有2个参数以匹配Child的WHERE子句，以及一个返回Parent.ID的投影.然后，主查询将包含3个用于过滤Parent的参数，并包含对子查询的调用以匹配Parent ID.

It will work like an inner select. The subquery will contain WHERE clause with 2 params to match Child, and a projection to return Parent.ID. The master query will then contain 3 params to filter Parent and also a call to subquery to match the Parent ID.

子查询:

var sub = DetachedCriteria
 .For<Child>()
 .Add(Restrictions.In("FirsChildProperty", new int[] {1, 2 })) // WHERE
 .Add(Restrictions.... // Second
 .SetProjection(Projections.Property("Parent.ID")); // Parent ID as a SELECT clause

主查询:

var criteria = session.CreateCriteria<Parent>()
 .Add(Restrictions.In("FirsParentProperty", new int[] {1, 2 })) // WHERE
 .Add(Restrictions.... // the second
 .Add(Restrictions.... // the third
 // no filter to match children
 .Add(Subqueries.PropertyIn("ID", sub)); // Parent.ID in (select
 // now paging just over Parent table....
 .SetFirstResult(100) // skip some rows
 .SetMaxResults(20)   // take 20

var result = criteria.List<Parent>();

这篇关于以一对多关系获取父项的最大记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！