NHibernate beginner HQL question

confuzatron · **Joined:** Thu Dec 06, 2007 12:35 pm **Posts:** 3

Hi,
I have a table Child which references table Parent via a foreign key.

When mapped to objects with NHibernate, this results in a collection 'Children' on object type Parent.

When I run the query:

select p from Parent p where p.Children.size != 0

I get a list of all the parents that have children.

but when I run the query:

select p, p.Children.size from Parent p where p.Children.size != 0

I get a list with a single item, containing one Parent object, and the count of the total number of records in the Child table.

So, can anyone tell me how to retrieve a list of all the parents with children, and for each one the number of associated children? Thanks.

xasp · **Posted:** Mon Dec 10, 2007 8:33 am

Hi,

Are you sure you only get back one parent ?

Given your query, you should get back an array of object, within an array of object (object[object[]]). The inner array is the couple parent and childcount while the outer array's length = number of parents.

enumerating the results should be something like

Code:

object[] results = ... (get the results from NHibernate)

foreach (object[] row in results)
{
     Parent p = (Parent)row[0];
     int count = (int)row[1];
}

confuzatron · **Joined:** Thu Dec 06, 2007 12:35 pm **Posts:** 3

The result I get is an ArrayList with one item, which is an object[] of length 2.

Using the nhibernate analyser program I found that the SQL is as follows:

(getting a single field 'name' of parent rather than the entire entity)

Code:

select 
   parent.name, count(*)
from 
   parent, child 
where 
   parent.id=child.api_method_id 
   and 
   (((select count(*) from child child2 where parent.id=child2.parent_id)!=0 ))

(cleaned up for readability and to reflect the fake table names in my example).

That count(*) in the select clause doesn't seem right to me.

(by the way, I'm using SQLite)

confuzatron · **Joined:** Thu Dec 06, 2007 12:35 pm **Posts:** 3

When I use Firebird instead of SQLite (SQL turns out the same), I get an exception trying to run the query:

Dynamic SQL Error
SQL error code = -104
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)

Which would suggest that maybe NHibernate is not generating legal SQL from the HQL?

xasp · **Posted:** Tue Dec 11, 2007 4:22 am

Hi,

Indeed, I don't think you can place the aggregate in the WHERE clause.

You can use the same query to retrieve (for example) the Id of the parents and the count of the children, but I don't think you can at the same time set a condition on the child-count:

Code:

SELECT p.Id,count(elements(p.Children)) FROM Parent p GROUP BY p.Id