mysql - 外部右连接在 NHibernate 中不起作用

Question

所以我有两个表:

表 1:

Item
id : int
title : varchar
comments : varchar

表 2:

Rating
id : int
isUpvote : bit
date : datetime
item_id : int

一个项目可以有多个评级。 IsUpvote 表示评级是喜欢还是不喜欢。

我正在使用 NHibernate 对我的 MySQL 数据库进行 ORM。

我想要制作一种项目趋势列表，以显示过去一段时间(从日期时间 X 到现在)最喜欢哪些项目。

MySQL 代码如下所示:

select p.id, sum(case when r.isUpvote = b'1' then 1 else 0 END) - sum(case when r.isUpvote=b'0' then 1 else 0 END) as score
from rating as r
right join item as p
on p.id = r.item_id
group by p.id
order by score desc
limit 7;

这导致:

Id - Score

1 - 2

2 - 1

3 - 0

4 - 0

5 - 0

6 - 0

7 - 0

所以我想在 NHibernate 中执行此操作。我试过了，但 HQL 和 QueryOver 但我就是想不通。我尝试的是:

GetTrendingItems(int nrOfTrendingItems, DateTime fromDate) {
var trendingItems = Session
            .CreateQuery(@"select p, sum(case when r.IsUpvote = 1 then 1 else 0 END) - sum(case when r.IsUpvote=0 then 1 else 0 END) as score
                from Item as p, Rating as r
                where p.Id = r.Point.Id
                group by p.Id
                order by sum(case when r.IsUpvote = 1 then 1 else 0 END) - sum(case when r.IsUpvote=0 then 1 else 0 END) desc")
            .SetMaxResults(nrOfClimbers)
            .List();
}

我试过了:

GetTrendingItems(int nrOfTrendingItems, DateTime fromDate) {
DBRating dbRatingAlias = null;
        var iets = Session.QueryOver<DBRating>(() => dbRatingAlias)
            .Where(r => r.Date > fromDate)
            .OrderBy(Projections.Conditional(
                Restrictions.Where(() => dbRatingAlias.IsUpvote),
                Projections.Constant(1),
                Projections.Constant(-1))).Desc
            .Right.JoinQueryOver<DBPoints>(r => r.Point)
            .Take(nrOfClimbers)
            .List();
}

我有点失去希望了。有人可以帮助我吗？

---

更新:HQL 生成此 SQL:

 select
    dbpoints0_.id as col_0_0_,
    sum(case 
        when dbrating1_.isUpvote=1 then 1 
        else 0 
    end)-sum(case 
        when dbrating1_.isUpvote=0 then 1 
        else 0 
    end) as col_1_0_,
    dbpoints0_.id as id3_,
    dbpoints0_.active as active3_,
    dbpoints0_.title as title3_,
    dbpoints0_.comments as comments3_,
    dbpoints0_.score as score3_ 
from
    points dbpoints0_,
    Rating dbrating1_ 
where
    dbpoints0_.id=dbrating1_.points_id 
group by
    dbpoints0_.id 
order by
    sum(case 
        when dbrating1_.isUpvote=1 then 1 
        else 0 
    end)-sum(case 
        when dbrating1_.isUpvote=0 then 1 
        else 0 
    end) desc limit ?p0;
?p0 = 10 [Type: Int32 (0)]

然后 Fluent 生成这个 SQL:

SELECT
    this_.id as id6_0_,
    this_.owners_id as owners2_6_0_,
    this_.points_id as points3_6_0_,
    this_.isUpvote as isUpvote6_0_,
    this_.date as date6_0_ 
FROM
    Rating this_ 
WHERE
    this_.date > ?p0 
ORDER BY
    (case 
        when this_.isUpvote = ?p1 then ?p2 
        else ?p3 
    end) desc limit ?p4;
?p0 = 25-5-2015 22:39:49 [Type: DateTime (0)],
?p1 = True [Type: Boolean (0)],
?p2 = 1 [Type: Int32 (0)],
?p3 = -1 [Type: Int32 (0)],
?p4 = 10 [Type: Int32 (0)]

Answer 1

我在de MySQL数据库中做了一个存储过程

DELIMITER //
create procedure getTrending
    (IN $getClimbers bool,
    IN $fromDate DateTime,
    IN $nrOfTrending int)
Begin
    if ($getClimbers) then
        select p.id, p.active, p.title, p.comments, 
        sum(case when r.isUpvote = b'1' and r.date > $fromDate then 1 else 0 END) - sum(case when r.isUpvote=b'0' and r.date > $fromDate  then 1 else 0 END) as score
        from rating as r
        right join points as p
        on p.id = r.points_id
        group by p.id
        order by sum(case when r.isUpvote = b'1' and r.date > $fromDate then 1 else 0 END) - sum(case when r.isUpvote=b'0' and r.date > $fromDate  then 1 else 0 END) desc
        limit $nrOfTrending;
    else
        select p.id, p.active, p.title, p.comments,
        sum(case when r.isUpvote = b'1' and r.date > $fromDate then 1 else 0 END) - sum(case when r.isUpvote=b'0' and r.date > $fromDate  then 1 else 0 END) as score
        from rating as r
        right join points as p
        on p.id = r.points_id
        group by p.id
        order by sum(case when r.isUpvote = b'1' and r.date > $fromDate then 1 else 0 END) - sum(case when r.isUpvote=b'0' and r.date > $fromDate  then 1 else 0 END) asc
        limit $nrOfTrending;
    end if;
end //
DELIMITER ;

然后从我的 C# 代码调用它:

public virtual List<DBPoints> GetClimbers(int nrOfClimbers, DateTime fromDate)
        {
            OpenSession();
            var query = Session.GetNamedQuery("getTrending");
            query.SetBoolean("getClimbers", true);
            query.SetDateTime("fromDate", fromDate);
            query.SetInt32("nrOfTrending", nrOfClimbers);
            var pointList = query.List<DBPoints>();
            CloseSession();
            return pointList.ToList();
        }

对于 Nhibernate，需要做一个这样的映射:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="TechDash.Business"
                   namespace="TechDash.Business.Models.Database">

  <sql-query name="getTrending">
<return class="DBPoints" />
call getTrending(:getClimbers, :fromDate, :nrOfTrending)

由于返回的列名称与检索点对象时返回的列名称相同，因此我不必创建新的 C# 对象或属于该 C# 的映射-对象。