NHibernate QueryOver为每个组实体选择一个实体

Question

我正在写turist博客的一部分，所以我有两个实体：位置和照片是1对多的关系，所以每个地方都可以有多张照片，但一张照片属于一个地方。

我的目标是创建摘要：最后添加了3个位置，每个位置我都想有一张照片(例如id较低的照片，这无关紧要(。重要的是，不仅要有照片的id，还要有更多的信息(列(，如照片标签、照片路径等。

在MS SQL 2008中使用SQL可以通过使用秩函数来完成：

select top 3 * from
(
SELECT     p.id, p.label, p.VisitDate, ph.id AS photo_id, ph.Label, ph.Path,
RANK() OVER (PARTITION BY p.id ORDER BY ph.id) AS _rank
FROM         place AS p INNER JOIN
                      photo AS ph ON p.id = ph.place_id
) ranked
where _rank = 1
order by VisitDate desc

或者可以在没有排名的情况下解决：

SELECT     TOP (3) a.id AS ida, z.id AS idz, z.etykieta, z.sciezka, a.data_odwiedzenia
FROM         place AS a INNER JOIN
                      photo AS z ON a.id = z.id_atrakcji
and z.id in 
(
SELECT  min(z.id) AS idz
FROM photo z 
where z.id_atrakcji in (select top 3 a.id from place a order by a.data_dodania desc)
group by z.id_atrakcji
)
ORDER BY a.data_odwiedzenia DESC

我开始写这个没有排名的查询，但我已经卡住了。

    summaryList =
 session.QueryOver(() => placeAlias)
.JoinAlias(o => o.Photos, () => photoAlias)
.SelectList(list => list
.Select(() => placeAlias.Id).WithAlias(() => summaryAlias.PlaceId)
.Select(() => placeAlias.Etykieta).WithAlias(() => summaryAlias.PlaceName)
.Select(() => photoAlias.Etykieta).WithAlias(() => summaryAlias.PhotoLabel)
.Select(() => photoAlias.Id).WithAlias(() => summaryAlias.PhotoId)
)
.OrderByAlias(() => placeAlias.VisitDate).Desc
.WithSubquery.WhereProperty(x => x.Id).In(lastPlaces)
.TransformUsing(Transformers.AliasToBean<PlacesSummary>())
.List<PlacesSummary>();

你能帮我做这个吗？如何编写lastPlaces queryover，或者可能还有其他方法？

谨致问候，Macko

Answer 1

var subquery = QueryOver.Of(() => photoAlias)
    .Where(photo => photo.Place.Id == placeAlias.Id)
    .Select(Projections.Max<Photo>(photo => photo.Id));
var summaryList = session.QueryOver(() => placeAlias)
    .OrderBy(() => placeAlias.VisitDate).Desc
    .JoinQueryOver(() => placeAlias.Photos, () => photoAlias)
    .WithSubquery.WhereProperty(photo => photo.Id).Eq(subquery)
    .SelectList(list => list
        .Select(() => placeAlias.Id).WithAlias(() => summaryAlias.PlaceId)
        .Select(() => placeAlias.Etykieta).WithAlias(() => summaryAlias.PlaceName)
        .Select(() => photoAlias.Etykieta).WithAlias(() => summaryAlias.PhotoLabel)
        .Select(() => photoAlias.Id).WithAlias(() => summaryAlias.PhotoId)
    )
    .TransformUsing(Transformers.AliasToBean<PlacesSummary>())
    .List<PlacesSummary>();

现在无法测试

Answer 2

它不能正常工作，但它非常接近：

summaryList =
session.QueryOver(() => placeAlias)
.OrderByAlias(() => placeAlias.VisitDate).Desc
.JoinAlias(o => o.Photos, () => photoAlias)
**.WithSubquery.WhereProperty(()=> photoAlias.Id).Eq(subquery)**
.SelectList(list => list
.Select(() => placeAlias.Id).WithAlias(() => summaryAlias.PlaceId)
.Select(() => placeAlias.Etykieta).WithAlias(() => summaryAlias.PlaceName)
.Select(() => photoAlias.Etykieta).WithAlias(() => summaryAlias.PhotoLabel)
.Select(() => photoAlias.Id).WithAlias(() => summaryAlias.PhotoId))
.TransformUsing(Transformers.AliasToBean<PlacesSummary>())
.Take(3)
.List<PlacesSummary>();

它只有一行不同。对于Subquery，它最初指的是Place，而应该指的是Photo。现在它正在工作，并生成以下SQL：

SELECT TOP (3)  this_.id as y0_, this_.PlaceName as y1_, photoalias1_.PhotoName as y2_, photoalias1_.id as y3_ 
FROM [place] this_ inner join [photo] photoalias1_ on this_.id=photoalias1_.id_place 
WHERE photoalias1_.id = (SELECT min(this_0_.id) as y0_ FROM [photo] this_0_ WHERE this_0_.id_place = this_.id) 
ORDER BY this_.DateVisit desc

它工作得很好，但我还有一个问题：

如何在QueryOver中重写这样的SQL：

SELECT  min(id)
FROM photo
where id in (... it doesn't matter what's here ...)
group by place_id

所以我只想要照片ID的列表(只有列(，这个查询将在另一个queryover中用作子查询。可以通过QueryOver语法完成吗？