按nhibernate中的收集计数排序 [英] ordering by collection count in nhibernate

问题描述

有没有通过属性集合的计数来排序对象列表的方法？

Is there a way of ordering a list of objects by a count of a property which is a collection?

对于参数，我们假设我有一个问题对象与一个问题名称属性，一个属性的答案对象的集合和另一个属性的用户对象的集合。用户通过问题表上的外键加入问题表，答案与中间联接表连接。

For arguments sake let's say I have a question object with a question name property, a property that is a collection of answer objects and another property that is a collection of user objects. The users join the question table via foreign key on question table and answers are joined with middle joining table.

如果我想让nhibernate得到一个问题对象的列表，我可以通过
Question.Answers.Count命令？

If I want nhibernate to get a list of "question" objects could I order it by Question.Answers.Count?

我已经尝试使用HQL的文档示例：

i've tried the documentation's example using HQL:

 List<Question> list = nhelper.NHibernateSession
        .CreateQuery("Select q from Question q left join q.Answers a group by q,a order by count(a)")
        .List<Question>();

但我得到

"column Question.Name is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" 

我已经尝试通过列表将所有属性添加到组，但它不工作。然后发生的是，外键userId导致与上面相同的错误，但我不能包括在组中作为nhibernate列为

I've tried adding all the properties to the group by list but it doesn't work. What happens then is that foreign key userId causes the same error as above but i can't include it in the group by as nhibernate lists it as

Question.Users.UserId

这不会解决它，如果包括。

which doesn't solve it if included.

任何想法？

推荐答案

我解决它的方式（你可以决定它是驴）是在数据库上使用UDF计算计数的计算列。然后我在hibernate映射中将列标记为update =false和insert =false。

the way i've solved it (and you may decide it's donkey) is to create a computed column on the database that calculates the count using a UDF. i then marked the column in the hibernate mapping as update="false" and insert="false".

我现在可以按照新列顺序排列我的查询。它给了我额外的好处，能够绑定这个值在需要它在我的应用程序的地方。

i can now order my queries by this new column as normal. It gives me the added bonus of being able to bind this value in places where it need it throughout my app.

它似乎执行ok，希望它不会花费我太多的性能。

it seems to perform ok, hopefully it isn't gonna cost me too much performance.

这篇关于按nhibernate中的收集计数排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！