Criteria querying the ones that have a many in many-to-one

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

Hi!

I'm using ICriteria with a Paginator class in a way, that the Paginator uses SetFirstResult, SetMaxResults and SetProjection to do it's work. I could do this with HQL, but then I would need to pass in at least two queries. One that selects the entities and one the counts them.

But now I need to do a slightly more complicated query and I'm not sure if this is possible with Criteria queries at all.

There are Product and a Department entities, with a many-to-one relationship. Now I want to select only those Departments, that contain at least one product. In HQL this could be done like this:

Code:

select from Department d where (select count(*) from Product p where p.Department = d) > 0

Is it possible to do such kind of request with a Criteria query?

Thx,
Tobias

mnichols · **Joined:** Mon Aug 21, 2006 9:18 am **Posts:** 179

Yes, you need to add a count Projection in a DetachedCriteria object for your Product class then add that as a Subqueries into your ICriteria for your Department. Let me know if you need code, but this kind of thing is a cinch with the Subqueries and Projections available now.

MIKE[/code]

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

Thanks a lot! This should work, but something is going wrong.

I'm doing this:

Code:

DetachedCriteria numberOfProductsInDepartment = DetachedCriteria.For(typeof(Product), "product")
      .SetProjection(Projections.RowCount())
      .Add(Expression.EqProperty("product.Department.Number", "department.Number"));

ICriteria criteria = session.CreateCriteria(typeof(Department), "department")
      .Add(Subqueries.Lt(0, numberOfProductsInDepartment));

...

criteria.SetFirstResult(0);
criteria.SetMaxResults(NHibernate.Engine.RowSelection.NoValue);
criteria.SetProjection(NHibernate.Expression.Projections.RowCount());
totalCount = criteria.UniqueResult<int>();

and get an NHibernate.ADOException (see below). When I run the query manually (SELECT count(*) as y0_ FROM Department this_ WHERE 0 < (SELECT count(*) as y0_ FROM Product this0__ WHERE this0__.Department = this_.Number)), it works just fine (using Firebird).

Any ideas, what may go wrong?

Tobias

PS: It looks like NH does not replace the "?" in the query with "0".

Code:

NHibernate.ADOException wurde nicht behandelt.
  Message="could not execute query\r\n[ SELECT count(*) as y0_ FROM Department this_ WHERE ? < (SELECT count(*) as y0_ FROM Product this0__ WHERE this0__.Department = this_.Number) ]\r\nPositinal Parameters:   0 0\r\n[SQL: SELECT count(*) as y0_ FROM Department this_ WHERE ? < (SELECT count(*) as y0_ FROM Product this0__ WHERE this0__.Department = this_.Number)]"
  Source="NHibernate"
  StackTrace:
       bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters)
       bei NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
       bei NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet querySpaces, IType[] resultTypes)
       bei NHibernate.Loader.Criteria.CriteriaLoader.List(ISessionImplementor session)
       bei NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria, IList results)
       bei NHibernate.Impl.SessionImpl.Find(CriteriaImpl criteria)
       bei NHibernate.Impl.CriteriaImpl.List()
       bei NHibernate.Impl.CriteriaImpl.UniqueResult()
       bei NHibernate.Impl.CriteriaImpl.UniqueResult[T]()

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

I did some more debugging and it seems it's Firebird that causes this problem. Somehow Firebird (or the .NETProvider) doesn't like to have a parameter before the subquery.

Tobias

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

I did some more debugging and it seems it's Firebird that causes this problem. Somehow Firebird (or the .NETProvider) doesn't like to have a parameter before the subquery.

Tobias

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

I did some more debugging and it seems it's Firebird that causes this problem. Somehow Firebird (or the .NETProvider) doesn't like to have a parameter before the subquery.

Tobias

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

I did some more debugging and it seems it's Firebird that causes this problem. Somehow Firebird (or the .NETProvider) doesn't like to have a parameter before the subquery.

Tobias

Tobi · **Joined:** Fri Sep 29, 2006 6:52 am **Posts:** 17

Just for the record - the problem is a possible Bug in Firebird, caused by a query parameter before a sub query (see http://tracker.firebirdsql.org/browse/CORE-1156)

As a workaround I now use an Exists sub query:

Code:

DetachedCriteria productsInDepartment = DetachedCriteria.For(typeof(Product), "product")
    .Add(Expression.EqProperty("product.Department.Number", "department.Number"))
    .SetProjection(Projections.Id());

ICriteria criteria = session.CreateCriteria(typeof(Department), "department")
    .Add(Subqueries.Exists(productsInDepartment));