Use a Criteria on top of another Criteria

Question

I have a question on criteria: How can i use a Criteria (or similar) that filters and/or do whatever with another criteria?

Something like:

select clients.* from (select * from clients) as clients

The real problem is something else, but achieving this behaviour would be terrific...

(btw, both java and .net are welcome to help)

thanks

Answer 1

Your FROM clause needs to be a mapped object. You could do a subselect inside the WHERE clause... something like:

select c from clients c where c.id in (select c2.id from clients c2)

It would help if you could give a better example. The example you gave can be reduced down to the following HQL:

"from clients"

...which isn't terribly useful.

Answer 2

It can't be done, AFAIK. The tutorial about HQL says:

Note that HQL subqueries can occur only in the select or where clauses.

I can't find same statement about Criteria, but in API the only way to create criteria is to give mapped type. There is support for subqueries but only in where clause. Here is javadoc.

Answer 3

i'm not sure i understand your question correctly, but if you want to do a select on a list of objects, you can use the subqueries with DetachedCriteria. I use it all the time, especially for paging objects while creating a left outer join, which could lead me to incorrect number of entities.

Imagine you've got users who buy products, with a relationship many-many:

Dim dc As DetachedCriteria = DetachedCriteria.For(GetType(User)).SetFirstResult(pageNumber * itemsPerPage).SetMaxResults(itemsPerPage) Session.CreateCriteria(GetType(user)).Add(Subqueries.PropertyIn("Id", dc)).CreateAlias("ProductsBought", "pb", NHibernate.SqlCommand.JoinType.LeftOuterJoin)

Thom's right, perhaps you should be more precise...

Answer 4

You could try adding a NHibernate.Criterion.InExpression to your criteria.

Found an example on this blog: http://www.lostechies.com/blogs/jimmy%5Fbogard/archive/2008/08/26/parameter-lists-in-nhibernate.aspx