[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_14791 ]
     
Fabio Maulo commented on NH-860:
--------------------------------

You can do something like:
select wv.PlannedGroup.Group, count(wv.Id) 
from AbstractSiteAddress asa, WorkingVisit wv 
inner join wv.PlannedGroup pg
inner join wv.Site st
group by wv.PlannedGroup.Group 
where (st.SiteAddress.id= asa.Id) and
(asa.TerritorySymbol = :lv1Val) and 
(asa.Municipality = :lv2Val)

To set parameter use
q.SetParameter("lv1Val", theValue); // theValue is a object instance
q.SetParameter("lv2Val", theOtherValue);

The method SetParameterList is for a clause like:
where asa.TerritorySymbol IN (:thisIsTheParameterList)

The query that you can see above (the first one) return a list of list tuple.
The first value of tuple is the value of property wv.PlannedGroup.Group and the second is an Int64 that represent count(wv.Id).

An HQL like
select count(wv.Id) from AClass ac where ac.AProperty = :myParam
return a list with only one element of type Int64.

For the first query, if you need a list of your own object type you can use your own NHibernate.Transform.IResultTransformer.

In general all you can do using criteria you can do the same, or better, using query.

I think this is not an issue.


> 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