Nhibernate标签云查询
到目前为止,这对我来说是一场为期两周的战斗,但没有运气。 :(
首先让我陈述我的目标。能够搜索标记为“foo”和“bar”的实体。不会认为这太难了吧?
我知道这可以轻松完成使用 HQL,但由于这是一个动态构建的搜索查询,因此不是一个选项。首先是一些代码:
public class Foo
{
public virtual int Id { get;set; }
public virtual IList<Tag> Tags { get;set; }
}
public class Tag
{
public virtual int Id { get;set; }
public virtual string Text { get;set; }
}
映射为多对多,因为 Tag 类用于许多不同的类型,因此
我构建了分离的 引用。为简单起见,我们假设我只是搜索带有标签“Apples”(TagId1) &&“Oranges”(TagId3) 的 Foos,这看起来像
SQL:
SELECT ft.FooId
FROM Foo_Tags ft
WHERE ft.TagId IN (1, 3)
GROUP BY ft.FooId
HAVING COUNT(DISTINCT ft.TagId) = 2; /*Number of items we are looking for*/
Criteria
var idsIn = new List<int>() {1, 3};
var dc = DetachedCriteria.For(typeof(Foo), "f").
.CreateCriteria("Tags", "t")
.Add(Restrictions.InG("t.Id", idsIn))
.SetProjection( Projections.ProjectionList()
.Add(Projections.Property("f.Id"))
.Add(Projections.RowCount(), "RowCount")
.Add(Projections.GroupProperty("f.Id")))
.ProjectionCriteria.Add(Restrictions.Eq("RowCount", idsIn.Count));
}
var c = Session.CreateCriteria(typeof(Foo)).Add(Subqueries.PropertyIn("Id", dc))
基本上,这是创建一个 DC,该 DC 投影具有指定的所有标签的 Foo Id 列表。
这在 NH 2.0.1 中编译,但不起作用,因为它抱怨找不到属性。 阅读 这篇 文章后,我对 Foo 类的“RowCount”
充满希望这可能会在 2.1.0 中得到修复,所以我升级了。 令我极其失望的是,我发现 ProjectionCriteria 已从 DetachedCriteria 中删除,并且我无法弄清楚如何在没有 DetachedCriteria 的情况下使动态查询构建工作。
因此,我尝试思考如何编写相同的查询,而不需要臭名昭著的 Have 子句。 可以通过标签表上的多个联接来完成。 万岁,我认为这很简单。 所以我重写它看起来像这样。
var idsIn = new List<int>() {1, 3};
var dc = DetachedCriteria.For(typeof(Foo), "f").
.CreateCriteria("Tags", "t1").Add(Restrictions.Eq("t1.Id", idsIn[0]))
.CreateCriteria("Tags", "t2").Add(Restrictions.Eq("t2.Id", idsIn[1]))
徒劳地尝试生成下面的 sql 来完成这项工作(我意识到它不太正确)。
SELECT f.Id
FROM Foo f
JOIN Foo_Tags ft1
ON ft1.FooId = f.Id
AND ft1.TagId = 1
JOIN Foo_Tags ft2
ON ft2.FooId = f.Id
AND ft2.TagId = 3
不幸的是,我在这次尝试中遇到了第一个障碍,收到了“重复关联路径”异常。 阅读周围这似乎是古老但仍然非常现实的错误/限制。
我缺少什么?
我开始诅咒 NHibernate 的名字,因为它让你认为如此简单和常见的查询变得如此困难。 请帮助以前做过此操作的人。 您是如何克服 NHibernate 限制的?
忘记声誉和赏金。 如果有人在这方面做得很好,我会寄给你 6 包,以解决你的麻烦。
This has been a 2 week battle for me so far with no luck. :(
Let me first state my objective. To be able to search entities which are tagged "foo" and "bar". Wouldn't think that was too hard right?
I know this can be done easily with HQL but since this is a dynamically built search query that is not an option. First some code:
public class Foo
{
public virtual int Id { get;set; }
public virtual IList<Tag> Tags { get;set; }
}
public class Tag
{
public virtual int Id { get;set; }
public virtual string Text { get;set; }
}
Mapped as a many-to-many because the Tag class is used on many different types. Hence no bidirectional reference.
So I build my detached criteria up using an abstract filter class. Lets assume for simplicity I am just searching for Foos with tags "Apples"(TagId1) && "Oranges"(TagId3) this would look something like.
SQL:
SELECT ft.FooId
FROM Foo_Tags ft
WHERE ft.TagId IN (1, 3)
GROUP BY ft.FooId
HAVING COUNT(DISTINCT ft.TagId) = 2; /*Number of items we are looking for*/
Criteria
var idsIn = new List<int>() {1, 3};
var dc = DetachedCriteria.For(typeof(Foo), "f").
.CreateCriteria("Tags", "t")
.Add(Restrictions.InG("t.Id", idsIn))
.SetProjection( Projections.ProjectionList()
.Add(Projections.Property("f.Id"))
.Add(Projections.RowCount(), "RowCount")
.Add(Projections.GroupProperty("f.Id")))
.ProjectionCriteria.Add(Restrictions.Eq("RowCount", idsIn.Count));
}
var c = Session.CreateCriteria(typeof(Foo)).Add(Subqueries.PropertyIn("Id", dc))
Basically this is creating a DC that projects a list of Foo Ids which have all the tags specified.
This compiled in NH 2.0.1 but didn't work as it complained it couldn't find Property "RowCount" on class Foo.
After reading this post I was hopeful that this might be fixed in 2.1.0 so I upgraded. To my extreme disappointment I discovered that ProjectionCriteria has been removed from DetachedCriteria and I cannot figure out how to make the dynamic query building work without DetachedCriteria.
So I tried to think how to write the same query without needing the infamous Having clause. It can be done with multiple joins on the tag table. Hooray I thought that's pretty simple. So I rewrote it to look like this.
var idsIn = new List<int>() {1, 3};
var dc = DetachedCriteria.For(typeof(Foo), "f").
.CreateCriteria("Tags", "t1").Add(Restrictions.Eq("t1.Id", idsIn[0]))
.CreateCriteria("Tags", "t2").Add(Restrictions.Eq("t2.Id", idsIn[1]))
In a vain attempt to produce the below sql which would do the job (I realise its not quite correct).
SELECT f.Id
FROM Foo f
JOIN Foo_Tags ft1
ON ft1.FooId = f.Id
AND ft1.TagId = 1
JOIN Foo_Tags ft2
ON ft2.FooId = f.Id
AND ft2.TagId = 3
Unfortunately I fell at the first hurdle with this attempt, receiving the exception "Duplicate Association Path". Reading around this seems to be an ancient and still very real bug/limitation.
What am I missing?
I am starting to curse NHibernates name at making what is you would think so simple and common a query, so difficult. Please help anyone who has done this before. How did you get around NHibernates limitations.
Forget reputation and a bounty. If someone does me a solid on this I will send you a 6 pack for your trouble.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
Ian,
由于我不确定您使用的是什么数据库后端,您能否对生成的 SQL 查询进行某种跟踪并查看 SQL 以找出问题所在?
我知道我过去这样做是为了了解 Linq-2-SQL 和 Linq-2-Entities 是如何工作的,并且能够调整某些情况以改进数据访问,以及理解为什么有些东西不能按最初预期工作。
Ian,
Since I'm not sure what db backend you are using, can you do some sort of a trace against the produced SQL query and take a look at the SQL to figure out what went wrong?
I know I've done this in the past to understand how Linq-2-SQL and Linq-2-Entities have worked, and been able to tweak certain cases to improve the data access, as well as to understand why something wasn't working as initially expected.
我设法让它像这样工作:
这里唯一的问题是计数(t1_.TagId) - 但我认为别名应该每次在这个 DetachedCriteria 中生成相同的 - 所以你应该为了安全起见,对其进行硬编码。
I managed to get it working like this :
The only problem here is the count(t1_.TagId) - but I think that the alias should be generated the same every time in this DetachedCriteria - so you should be on the safe side hard coding that.