Get tags like Stackoverflow with NHibernate

Question

My model is like this:

Flavor
  public IList<Mention> Mentions

Mention
  public IList<Flavor> Flavors

Therefore, a many-to-many between Flavor and Mention.

I need to create a query that returns me the flavors mentioned and the amount of times it was mentioned.

Today, I can get the query terms ordered by the number of flavors mentioned but can not return the whole lot of times.

My hql query:

  select flavor
  from Flavor flavor
  left join flavor.Mention mentions
  group by flavor.Id, flavor.Name
  order by count(mentions) desc

Does anyone know how to do this?

Answer 1

Try this:

var flavors = Session.CreateQuery(@"select f.Id, f.PropertyName, count(elements(f.Mentions)) 
                                   from Flavor f where exists elements(f.Mentions) group by f.Id, f.PropertyName").List();

I believe it does the trick...