Need some help ...

I have two classes - Parent and Child (associated using one-to-many in Parent.hbm.xml and many-to-one in Child.hbm.xml). In the object model, children is an IList of Child in a Parent object.

Need to populate a gridview(asp.net) with results from searching on both the Parent's and Child's attributes:
"select distinct p from Parent as p left join p.children as c where p.Name = :name and c.Age = :age ..." etc;

Now, there are about 100,000 rows in the Parent table, which makes a query.List() from this above HQL slow. I need only 20 records at a time, when the user is paging through search results. However, if the user removes all search criteria, this HQL fetches all 100,000 rows from the table. Is there a easy way to limit this?

Unable to use getFirstResult() and SetMaxResults() because this is a left join, and these functions operate on the join results, and not after applying 'distinct'. (http://www.hibernate.org/117.html#A12)

Hibernate version:1.2
Name and version of the database you are using:Oracle 10g

I have found this behavior to be unintuitive and, quite frankly, a pain in the arse.

Regardless, the workaround is to use a subquery if your DB supports it:


Then you can use SetFirstResut and SetMaxResults as normal.

Hopefully that will solve your problem.

Cheers,

Symon.