使用 NHibernate ICriteria 或 QueryOver 按聚合子查询排序
有没有办法用 NHibernate ICriteria 或 QueryOver 实现这样的 SQL？
select *
from [BlogPost] b
inner join (select blogpost_id, count(*) matchCount
from [Tag]
where name in ('tag X', 'tag Y')
group by blogpost_id
) tagmatch
on tagmatch.blogpost_id = b.Id
order by tagmatch.matchCount desc
目的是根据匹配标签的数量对博客文章进行排名，以便同时具有 标签 X 和 标签 Y 的帖子位于仅具有 标签 X 的帖子之上嗯>。
到目前为止我已经得到了这个：
DetachedCriteria
.For<Tag>("tag")
.Add(Restrictions.In(Projections.Property<Tag>(x => x.Name), tags.ToArray()))
.SetProjection(Projections.Group<Tag>(t => t.BlogPost))
.CreateCriteria("BlogPost")
.SetFetchMode("BlogPost", FetchMode.Eager)
.AddOrder(Order.Desc(Projections.RowCount()));
但是，生成的查询没有加入 fetch BlogPost。相反，它只返回 ids，这会导致在迭代 BlogPost 时选择 n+1。
public class BlogPost
{
...
ISet<Tag> Tags {get; set;}
}
public class Tag
{
BlogPost BlogPost { get; set; }
string Name { get; set; }
}
< strong>NHibernate 3 现在可以实现这一点吗？
如果不能，是否有替代解决方案？
我可以更改架构和域模型（如果需要）。如果可能的话我不想使用 SQL 或 HQL。
Is there a way to achieve SQL like this with NHibernate ICriteria or QueryOver?
select *
from [BlogPost] b
inner join (select blogpost_id, count(*) matchCount
from [Tag]
where name in ('tag X', 'tag Y')
group by blogpost_id
) tagmatch
on tagmatch.blogpost_id = b.Id
order by tagmatch.matchCount desc
The aim is to rank blog posts by the number of matching tags so that a post with both tag X and tag Y comes above posts with just tag X.
I've got this so far:
DetachedCriteria
.For<Tag>("tag")
.Add(Restrictions.In(Projections.Property<Tag>(x => x.Name), tags.ToArray()))
.SetProjection(Projections.Group<Tag>(t => t.BlogPost))
.CreateCriteria("BlogPost")
.SetFetchMode("BlogPost", FetchMode.Eager)
.AddOrder(Order.Desc(Projections.RowCount()));
However, the resulting query doesn't join fetch BlogPost. Instead it returns just the ids, which leads to select n+1 when the BlogPosts are iterated.
public class BlogPost
{
...
ISet<Tag> Tags {get; set;}
}
public class Tag
{
BlogPost BlogPost { get; set; }
string Name { get; set; }
}
This looks like a similar issue.
Is this now possible with NHibernate 3?
If not, is there an alternative solution?
I can change schema & domain model if necessary. I don't want to use SQL or HQL if possible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我知道这个问题是不久前提出的，但我想做同样的事情，请看看我的问题这里，还有这个人这里，也许你可以使用这个想法。
I know this question was put some time ago, but I want to do about the same thing, please take a look to my question here, and this guy here, maybe you can use the idea.