NHibernate: hql to criteria query - help needed

Question

Hello there,

I have this hql query, which works perfect:

            select m 

            from Media m

            join m.Productlines p
            join m.Categories c                
            join m.Spaces sp
            join m.Solutions so

            where m.Uid != 0
            and p.Uid in (:productlines)
            and c.Uid in (13)                
            and sp.Uid in (52)
            and so.Uid in (15,18)

            group by m.Uid

But now it needs to be parameterized/made dynamic, not only the parameters, but also the joins (it is possible to select only from Media, without any joins, and so no *.Uid in will be required in this case).

I dont want to mess around with a StringBuilder instance and build the hql query that way, I would rather like to use the Criteria API, but I cant get a

SELECT m.*
....
GROUP BY m.Uid

query to work with Criteria.

If I add a

Projections.GroupProperty("Uid")

to my query, nhibernate selects

SELECT m.Uid
....
GROUP BY m.Uid

which is of course wrong.

After that, I also need to count the unique rows the query returned, as the result is paged.

So, my other query is quite similiar, but I cant find a Criteria equivalent for

SELECT COUNT(DISTINCT m.Uid)

Here is the HQL:

            select count(distinct m.Uid) 

            from Media m

            join m.Productlines p
            join m.Categories c                
            join m.Spaces sp
            join m.Solutions so

            where m.Uid != 0
            and p.Uid in (:productlines)
            and c.Uid in (13)                
            and sp.Uid in (52)
            and so.Uid in (15,18)

How can this be done with Criteria API?

Please, (N)Hibernate experts - help me with this, I cant find a working solution. Any help is greatly appreciated!

Answer 1

var count = session.CreateCriteria(typeof(Media))
    // Add other criterias...
    .SetProjection(Projections.CountDistinct("Id")) // or whatever the id property of Media class is called
    .UniqueResult<long>();

As to your GROUP BY question, the query:

SELECT m.*
....
GROUP BY m.Uid

makes no sense because you need to select only columns that appear in the group by clause or aggregate functions. Could you elaborate a little more as to what exactly are you trying to achieve?

Answer 2

Group columns are implicitly returned as result, but you can add more columns. AFAIK, you can return full entities:

var query = session.CreateCriteria(typeof(Media), "m")
  .Add(Projections.GroupProperty("m"))
  .Add(Restrictions.NotEq("m.Uid", 0));

// dynamically add filters
if (filterProductLines)
{
  query
    .CreateCriteria("m.Productlines", "p")
    .Add(Restrictions.Eq("p.Uid", productLines));
}
// more dynamic filters of this kind follow here...

IList<Media> results = query.List<Media>();

To count the full number of results you can just build up the same query with different projection:

var query = session.CreateCriteria(typeof(Media), "m")
    .SetProjection(Projections.CountDistinct("m.Uid"));
// rest of the query the same way as above

long totalNumberOfResults = query.UniqueResult<long>();

---

I'm getting unsure about the Projections.GroupProperty("m"), you need to try this. If it doesn't work, you could make it an DetachedQuery that only returns ids:

var subquery = DetachedCriteria.For(typeof(Media), "m")
  .Add(Projections.GroupProperty("m.Uid"))
  .Add(Restrictions.NotEq("m.Uid", 0));
// add filtering

var query = session.CreateCriteria(typeof(Media), "outer")
  .Add(Subqueries.PropertyIn("outer.Uid", subquery));

IList<Media> results = query.List<Media>();

This creates a sql query like this:

select outer.* // all properties of Media to create an instance
from Media outer
where outer.Uid in (
  select Uid
  from media m
  where // filter
)