Hibernate version: 3.1.2

There doesn't seem to be any way of performing a criteria query similar to:

Q1: from Parent p where exists(from p.children c where c.shoesize >= 10)

The closest I can get is:
Criteria crit = session.createCriteria(Parent.class);
Criteria subCrit = crit.createCriteria("children");
subCrit.add(Expression.ge("shoesize", 10));
crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);

but that's not quite the same, it's more like:

from Parent p join p.children c where c.shoesize >= 10

sure in this particular instance it will produce the same results, but if I had somthing like:

Q2: from Parent p where 2 <= (select count(*) from p.children c where c.shoesize >= 10)

there's no way that I can see that it can be done with criteria queries.

Has anyone got any solution?


Mapping documents:
NB: I generaly use annotations, but you'll get the gist:

Code:

<hibernate-mapping package="org.test">
   <class name="Parent">
      <id name="id">
         <generator class="auto"/>
      </id>
      <property name="name" not-null="true"/>
      <set name="children" cascade="delete">
         <key column="parentId"/>
         <one-to-many class="Child"/>
      </set>
   </class>
   <class name="Child">
      <id name="id">
         <generator class="auto"/>
      </id>
      <property name="shoesize"/>
      <many-to-one name="parent" column="parentId"/>
   </class>
</hibernate-mapping>

It's not simple, but it can be done. Check the code example titled "Even correlated subqueries are possible:" in section 15.8, "Detached queries and subqueries", of the ref docs.

Yes I looked at that.

I assume you're suggesting somthing like the equivalent of:

from Parent p
where exists(from Child c where c in p.children and c.shoesize >= 10)

This can't be expressed, there's Restrictions.in which finds if a property is in some externaly defined collection of values, and there's Subqueries.propertyIn which matches a property to a subquery, but there's no way of creating a subquery from a property without joining it to the parent class, so the "c in p.children" part can't be expressed.

We'd either need a Restrictions.inProperty or better yet a method like

DetachedCriteria.fromParentPath("children");

I was considering your second example, since (as you pointed out) "from Parent p where exists(from p.children c where c.shoesize >= 10)" is functionally equivalent to "from Parent p join p.children c where c.shoesize >= 10". DetachedCriteria can be use to express subqueries using the Property class. I haven't done this, but something along these lines is needed (probably):

So you agree that Q2 (in the original post) cannot be expressed in the criteria API as written?

What I'm realy getting at is that the Criteria API as it stands doesn't provide any way to subselect within a collection on the parent. If you rely on joins and Criteria.DISTINCT_ROOT_ENTITY there are some things that are at best messy and at worst impossible to express.

If this is true then this is a crucial missing feature, which realy should be added to the JIRA issue database.

It's true you could reexpress Q2 somthing like:

select p
from Parent p join p.children c
where c.shoesize >= 10
group by p
having count(*) > 2

but this isn't realy my point. Throw in a few more sub selects and the resulting query gets messier and more complicated, and I'm sure in some cases impossible. For example try expressing this without using subselects:

from Parent p
where 2 <= (from p.children c where c.shoesize >= 10)
or (p.age*2) > all (select c.age from p.children where c.shoesize < 5)

I am dealing with a similar problem, I can't quite tell if it is exactly what you have or not. I need a query to return a parent class (based on ids) containing only a subset of one of it's child lists based on a date range. IE something like:



However this query ONLY selects parents that have children within that range. I'd like it to select all the parents with id in :ids, and then of those parents select only children for the parents that fall within the date range. Is this doable?

You're grabbing the thing from the wrong end, try:



or if there's no mapping from child to parent:



but this has nothing to do with my issue, which is to do with a missing feature in Criteria queries.

Sorry to hijack your thread.. back to the topic =)

swamp_ig, I think that Critera do provide a way to do subselects within a parent's collection, and that my earlier example (the one that uses Property.in(DetachedCriteria)) is something like the way to do it. Have a go at solving your problem with some variation on my sample code.

Property.forName() works only with properties, not collections.

I'm pretty sure of what I'm saying here, there's simply no way of expressing Q1 in the criteria API. There's a few bits of evidence:

1:


This doesn't compile since Subqueries.exists expects a detached criteria.

2: If you comment the non-compiling line in the above the resulting SQL shows a join between the two tables. If the exists were to work this join couldn't be there, the property path would have to exist in the sub-select only.

3: There's no way to get around it. If we had Restrictions.inProperty we could do somthing like:



or as HQL:

Q3: from Parent p where exists(from Child c where c in p.children and ...)

but there's no in restriction which restricts to a existing collection, they only take either a subquery (no good to us) or a set of values (also no good).

If you don't believe this is correct please give me some code rather than a vauge assertion to the contrary, otherwise I'll submit this as a bug.

Is this close to correct? I have a MeterPoint class, containing a Set of Installations. Installations have start dates and end dates. I want to select all meter points that have installations date ranges between 2 and 1 years ago, and where the meter point name begins with H:This generated the follow SQL, which looks correct to me:Unfortunately I'm a bit pressed for time today, so I haven't compared the results with what I'd expect, but even if it's not right, it's close. I'm sure I could get the right results from this.

However, it's an excessively complex solution. You could submit inProperty as a development request?

Note that I did encounter one bug-like issue, in that the subquery required a projection. It threw an exception until I added the dummy one.

Nope. The subquery here is not correlated with the containing query at all. The subquery clause would have to look somthing like "exists(from parent.parent_collection where ...". IE: checking for some criteria being true in one of the collections on the parent.

The clue here is that the generated SQL doesn't refer to the containing table.

Right them, criteria where the subselect clause includes comparison with tables in the principal select:

MeterPoint contains a reference to Location, which contains a reference to an Address. This criteria finds all meter points that are named after the city they're in, so long as it begins with W:This generates the following SQL:It tested it this time, it works. this0__ is the Address in the subquery, this_ is the meter point from the main query, and they're compared in the subquery. Is that what you wanted?

That will produce somthing more like it in this case, but it still requires rearranging the original query in odd ways. Address still appears as a join in the outer query. In this particular example it's not going to be a problem, (asuming you use DISTINCT_ROOT_ENTITY as a filter) but there are cases where it will prove difficult.

for example the following HQL:



(in english: parent's age is either twice the average age of the children over ten, or they have two children under 5)

If we only had the bit before the first OR it wouldn't be such a problem, you could even get rid of the subselect entirely and use group by and having to replace it. But as soon as you try and filter the same collection in two different ways the rearangement becomes impossible.

So what I'm looking for is a criteria query which contains a subselect that refers to a collection owned by the parent, but without the parent containing any joins. The joins appear in the subselect only.

Anyhow, given the continued fragility of the criteria API I've given up and am just concatenating bits of HQL together like I ended up doing in the last project.