Nhibernate Tag Cloud Query

Question

Hi,

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.

Answer 1

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.

Answer 2

I managed to get it working like this :

var dc = DetachedCriteria.For<Foo>( "f")
   .CreateCriteria("Tags", "t")
   .Add(Restrictions.InG("t.Id", idsIn))
   .SetProjection(Projections.SqlGroupProjection("{alias}.FooId", "{alias}.FooId having count(distinct t1_.TagId) = " + idsIn.Count,
       new[] { "Id" }, 
                           new IType[] { NHibernateUtil.Int32 }));

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.