NHibernate Criteria按另一个表中的分组依据和itemid的总和来选择项目 [英] NHibernate Criteria select items by the group by and sum of itemid within another table

问题描述

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; }
}

我正在尝试根据SearchTextLog中计数的总和来选择前5个SearchText项目.目前，我只能通过先执行查询以获取前5个项目，然后在第二个查询中使用结果来解决此问题.我想知道是否有人可以向我展示信息并教我如何将这两个独立的查询集成到一个单元中.

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.

这是我目前拥有的:

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>();

哦，不，我只是意识到我当前的解决方案将失去结果的重要顺序.因此，我绝对必须合并查询. :-/

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. :-/

我也尝试了双向映射以允许以下语句，但是，我无法获取它来返回SearchText项目.它只是抱怨SearchText属性不在分组中.

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>();

请原谅我的无知，但是Nhibernate对我来说是全新的，需要完全不同的思维方式.

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

推荐答案

好的，我想我已经找到了解决方案.

Ok, I think I have figured out a solution.

按照我的问题，我的原始解决方案将无法正常工作，因为NHibernate尚不支持在不将其添加到select子句的情况下进行按属性分组的功能(请参阅:

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).

然而，在鬼混的时候，我遇到了这些很酷的东西，叫做ResultTransformers.使用AliasToBean结果转换器，Nhibernate会自动将我赋予每个投影项的别名映射到我指定类型内具有相同名称的属性.我只是指定了SearchText对象(但是，我必须为总投影项目添加一个额外的TotalSearchCount属性).它完美地填充了我的对象并将其退回.

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>();

我很惊讶这并不容易做到.我花了大约4到5个小时的研究和开发才能弄清楚这一点.希望我的NHibernate经验会随着越来越多的经验而变得更加轻松.

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!

这篇关于NHibernate Criteria按另一个表中的分组依据和itemid的总和来选择项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！