In the HQL documentation they give this example:
Code:
select cat.id, (select max(kit.weight) from cat.kitten kit) from Cat as cat
If I am reading the HQL syntax correctly, this query has a subquery that selects from the kitten's association of the cat from the outer query.
I need to do something similar using NHibernate's Criteria API. For simplicity's sake, I will continue to use the cat example. The query I need would look something like this in HQL:
Code:
select cat from Cat as cat where 1 < (select count(kit.id) from cat.kitten kit)
According to the documentation, the way to create a subquery is to create a DetachedCriteria. But, when I create a DetachedCriteria for my subquery, I only have the option to specify the Entity class that I'm querying from. I don't have the option to specify that I'm querying from an association of an object from the outer query.
If the association is bidirectional, then one could do something like this to get the desired result:
Code:
select cat from Cat as cat where 1 < (select count(kit.id) from Cat kit where kit.Parent = cat)
However, in my case, the associations wont always be bidirectional. Is there an effecient and more general way of performing this query using the Criteria API? Since it is fairly simple in HQL, it seems like it should be possible in the Criteria API. I've been working with it for about three says now, and most of it have been fairly straightforward. But, I haven't been able to figure out how to do this kind of query in the Criteria API.
Hibernate version: 2.0.0.GA
Mapping documents: N/A
Code between sessionFactory.openSession() and session.close(): N/A
Full stack trace of any exception that occurs: N/A
Name and version of the database you are using: Microsoft SQL Server 2000
The generated SQL (show_sql=true): N/A
Debug level Hibernate log excerpt: N/A
Code:
Login
FAQ
Search