NHibernate Criteria select items by the group by and sum of itemid within another table.

Question

public class SearchText
{
    public virtual int Id { get; set; }
    public virtual string Text { get; set; }
}

public class SearchTextLog
{
    public virtual int Id { get; set; }
    public virtual SearchText SearchText { get; set; }
    public virtual User User { get; set; }
    public virtual int SearchCount { get; set; }
    public virtual DateTime LastSearchDate { get; set; }
}

I am trying to select the top 5 SearchText items based on the sum of their count within the SearchTextLog. Currently I have only been able to resolve this by first performing a query to get the top 5 items, and then using the result within a second query. I was wondering if someone could show me the light and teach me how I could integrate these two seperate queries into a single unit.

Here is what I have currently:

var topSearchCriteria = Session.CreateCriteria(typeof (SearchTextLog))
            .SetProjection(Projections.ProjectionList()
                            .Add(Projections.GroupProperty("SearchText.Id"))
                            .Add(Projections.Alias(Projections.Sum("SearchCount"), "SearchCount")))
            .AddOrder(Order.Desc("SearchCount"))
            .SetMaxResults(topSearchLimit)
            .List<int>();

return Session.CreateCriteria<SearchText>()
            .Add(Restrictions.In("Id", topSearchCriteria.ToArray()))
            .List<SearchText>();

Edit:

Oh no, I just realised my current solution will lose the important order by of the results. So I will definitely have to incorporate the queries. :-/

Edit:

I tried a bidirectional mapping too to allow the following statement, however, I can't get it to return SearchText items. It simply complains that the SearchText properties aren't in a grouping.

return Session.CreateCriteria<SearchText>()
                .CreateAlias("SearchTextLogs", "stl")
                .AddOrder(Order.Desc(Projections.Sum("stl.SearchCount")))
                .SetMaxResults(topSearchLimit)        
                .SetResultTransformer(Transformers.AliasToEntityMap)
                .List<SearchText>();

Excuse my ignorance, but Nhibernate is completely new to me, and requires a completely different way of thinking.

Answer 1

doesn't this work?

var critterRes = Session.CreateCriteria(typeof (SearchTextLog))
            .SetProjection(Projections.ProjectionList()
                            .Add(Projections.GroupProperty("SearchText"))
                            .Add(Projections.Property("SearchText"))
                            .Add(Projections.Alias(Projections.Sum("SearchCount"), "SearchCount")))
            .AddOrder(Order.Desc("SearchCount"))
            .SetMaxResults(topSearchLimit)
            .List<SearchText>()

Answer 2

Ok, I think I have figured out a solution.

My original solution as per my question won't work because NHibernate doesn't yet support the ability to do a group by property without adding it to the select clause (see: link text).

While fooling around however, I came across these cool things called ResultTransformers. Using the AliasToBean result transformer Nhibernate will automatically map the alias's I give to each projection item to properties by the same name within a type I specify. I simply specified my SearchText object (however, I had to add an additional TotalSearchCount property for the sum projection item). It populated my objects perfectly and returned them.

return Session.CreateCriteria(typeof(SearchTextLog))
            .CreateAlias("SearchText", "st")
            .SetProjection(Projections.ProjectionList()
                                .Add(Projections.Alias(Projections.GroupProperty("st.Id"), "Id"))
                                .Add(Projections.Alias(Projections.GroupProperty("st.Text"), "Text"))
                                .Add(Projections.Alias(Projections.Sum("SearchCount"), "TotalSearchCount")))
            .SetMaxResults(topSearchLimit)
            .AddOrder(Order.Desc("TotalSearchCount"))
            .SetResultTransformer(Transformers.AliasToBean(typeof(SearchText)))
            .List<SearchText>();

I am surprised this wasn't easier to do. It's taken me about 4 to 5 hours of research and dev to figure this one out. Hopefully my NHibernate experience will get easier with more and more experience.

I hope this helps someone else out there!