Report query to select member from objects in collection

tomas.tintera · **Posted:** Mon Jan 29, 2007 5:40 pm

NH 1.2b3 / MS SQL 2005 / Later planned Oracle:

Dear all,
I want to create reporting query, where I can select cetain property of a collection. (Collection is used in many-to-many relationship)

Desired query is something like:

Code:

select g.MemberUsers.Id from GxGroup g where g.Id in {0}

Is possible to write such query in HQL or do I need to use SQL?

I understand this query is somehow atipical in O/R maper and NH, but need such a query to create single report.

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

If you're just getting the ID of the objects in the collection, then hibernate won't fetch the entire collection, it'll just get the correct values from the join table. If you want any other properties, use SQL queries for performance, as HQL queries will cause all columns in the collection's table to be selected.

Usually, the performance hit is trivial, so I'd recommend going with HQL and testing the results.

tomas.tintera · **Posted:** Tue Jan 30, 2007 12:21 pm

Thank you for your answer. NH behaviour is perfect, but in this case I preffer to select IDs from multiple collections at once. At least for performance testing.

Tryed SQL question, but got exception.
SQLquery is

Code:

session.CreateSQLQuery(select distinct {g.memberId from GxGroupInGroup where masterGroupId in (1,2,3,4),  "",           typeof(long));

Exception is

Code:

No persister for: System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"}   System.Exception {NHibernate.MappingException}

Thank you in advance for comments.

Tomas Tintera

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Looks like you haven't got a persister for Int64. In fact, the {g.memberId} (I'm assuming that there's really a "}" in there) looks odd to me: isn't {} notation for retrieving entire objects? Don't you want addScalar("memberId", Hibernate.LONG), or whatever the NHibernate equivalent is?

tomas.tintera · **Posted:** Wed Jan 31, 2007 6:18 am

tenwit wrote:

Looks like you haven't got a persister for Int64. In fact, the {g.memberId} (I'm assuming that there's really a "}" in there) looks odd to me: isn't {} notation for retrieving entire objects? Don't you want addScalar("memberId", Hibernate.LONG), or whatever the NHibernate equivalent is?

In fact any example of NH SQL query returning scalar would save me.

Continuing to figure ho to make it. I tryed the AddScalar method, but still have an exception of missing persiter.

Code:

session.CreateSQLQuery("select distinct memberId from GxUserInGroup where masterGroupId in (1,2,3,4)").AddScalar("memberId", NHibernateUtil.Int64).List();

Code:

"No persister for: System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"   "No persister for: System.Int64, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"   string

But it's a NHibernates build-in type. So should I create persister for it? Unfortunatelly there is nodocs on this topic. And I can't even gogle any example use of SQL query returning scalar.