[nhibernate-issues] [JIRA] Commented: (NH-860) Performant count of HQL query

[NHibernate J. <mik...@us...>]

     [ http://jira.nhibernate.org/browse/NH-860?page=comments#action_14790 ]
     
Peter Smulovics commented on NH-860:
------------------------------------

Try the Multiquery feature, there are some examples on using this:

1195    IMultiQuery multiQuery = s.CreateMultiQuery()
1196        .Add(s.CreateQuery("from Item i where i.Id > ?")
1197                .SetInt32(0, 50).SetFirstResult(10))
1198        .Add(s.CreateQuery("select count(*) from Item i where i.Id > ?")
1199                .SetInt32(0, 50));
1200    IList results = multiQuery.List();
1201    IList items = (IList)results[0];
1202    long count = (long)((IList)results[1])[0];

or with named params:

1210    IList results = s.CreateMultiQuery()
1211        .Add(s.CreateQuery("from Item i where i.Id > :id")
1212            .SetFirstResult(10) )
1213        .Add("select count(*) from Item i where i.Id > :id")
1214        .SetInt32("id", 50)
1215        .List();
1216    IList items = (IList)results[0];
1217    long count = (long)((IList)results[1])[0];


> Performant count of HQL query
> -----------------------------
>
>          Key: NH-860
>          URL: http://jira.nhibernate.org/browse/NH-860
>      Project: NHibernate
>         Type: Improvement
>   Components: Core
>     Versions: 1.2.0.Beta3
>     Reporter: Luis Ferreira

>
> We are implementing pagination in our Web Project and we'd like to have a way of calculating the count(*) for each of the HQL queries we have already implemented *without writing specific queries for that* AND *without returning the results as a list* and then getting its count property.
> A member of the team has attempted, with some success, to use NHibernate to
> generate the SQL for our HQL queries and then wrapping it with a select
> count(*) from (<generated SQL>). But to do that we have had to use
> Reflection to access protected methods, and filling query parameters for
> parametrized queries is still a problem. To really tackle the issue it would
> be necessary to have some changes in NHibernate, namely public exposure in
> IQuery of methods in AbstractQueryImpl / QueryImpl regarding access to named
> parameters names and values.
> Of course a really great solution would be for NHibernate to provide that
> count(*) funcionality (in a way similar to what has been done with
> projections for ICriteria? haven't used that much, we've got really hairy
> queries to write). It seems to be an issue for a lot of people. For
> databases that support subquerying it wouldn't be too difficult to
> implement I guess, but maybe the team doesn't want to impose that kind of
> restrictions.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://jira.nhibernate.org/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira