C＃/ NHibernate的 - 取得前10个记录由分组总和排序 [英] C#/NHibernate - Get first 10 records ordered by grouped sum

问题描述

好了，基本上我试图找出一种方式来获得的10最畅销的产品。我的东西可以很容易地用下面的SQL查询实现：

Well, basically I'm trying to figure out a way to get the 10 most sold products. Something I could easily achieve with the following SQL Query:

SELECT `product`.*
FROM `product`
INNER JOIN `sale_item` ON `product`.`id` = `sale_item`.`product_id`
GROUP BY `product`.`id`
ORDER BY SUM(`sale_item`.`quantity`) DESC
LIMIT 10;

我与NHibernate的成功最接近的，我相信是这样的：

The closest I got to succeeding with NHibernate, I believe was this:

ICriteria criteria = NHibernateSession
    .CreateCriteria<SaleItem>("SaleItem")
    .SetMaxResults(10)
    .CreateCriteria("ID.Product")
        .SetProjection(Projections.ProjectionList()
            .Add(Projections.GroupProperty("ID.Product"))
            .Add(Projections.Sum("SaleItem.Quantity"), "QuantitySum")
        )
        .AddOrder(Order.Desc("QuantitySum"));

List<Product> l = criteria
    .List<Product>() as List<Product>;

这生成以下SQL（非常类似于雷）：

Which generated the following SQL (quite similar to mine):

SELECT this_.product_id AS y0_,
       sum(this_.quantity) AS y1_
FROM sale_item this_
INNER JOIN product product1_ ON this_.product_id=product1_.id
GROUP BY this_.product_id
ORDER BY y1_ DESC LIMIT 10;

不幸的是，在执行查询时失败。我敢肯定它有话跟我做 .CreateCriteria< SaleItem> ，然后询问的.List<产品> ，但我不知道该怎么做它的其他方式。

Unfortunately, it failed when executing the query. I'm pretty sure it has something to do with me doing .CreateCriteria<SaleItem> and then asking .List<Product>, but I don't know how to do it the other way.

任何帮助深表感谢。

推荐答案

您可以使用 Transformers.AliasToBean<产品>（）结果变压器：

You can use Transformers.AliasToBean<Product>() result transformer:

ICriteria criteria = NHibernateSession
    .CreateCriteria<SaleItem>("SaleItem")
    .SetMaxResults(10)
    .CreateCriteria("ID.Product")
        .SetProjection(Projections.ProjectionList()
            .Add(Projections.GroupProperty("ID.Product"), "ID")
            .Add(..., "...") // another Product property
            .Add(Projections.Sum("SaleItem.Quantity"), "QuantitySum")
        )
        .AddOrder(Order.Desc("QuantitySum"));

List<Product> l = criteria
    .SetResultTransformer(Transformers.AliasToBean<Product>());
    .List<Product>() as List<Product>;

这篇关于C＃/ NHibernate的 - 取得前10个记录由分组总和排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！