Count Elements Conditionally In HQL
I have an HQL query that returns projections of an entity including a count of the a certain child collection.
select t.Id as TagId, t.Name as Name, count(elements(t.Documents)) as Count
from Tag t
group by t.Id, t.Name, t.User
having t.User.Id = :userId"
It works except I need to place a condition on the count. I only want to count Documents where Trashed = false for the document.
I seem to have a mental block on how to do this. I am using NHibernate 3.0. I would be equally happy with ICriteria or Linq query.
UPDATE: Here is the fully correct开发者_运维技巧 query:
select t.Id as TagId, t.Name as Name, sum(case when d.Trashed = false then 1 else 0 end) as Count
from Tag t
left join t.Documents d
group by t.Id, t.Name, t.User
having t.User.Id = :userId and sum(case when d.Trashed = false then 1 else 0 end) > 0
IIRC, something like this should work (where d is an alias to the Documents collection):
sum(case when d.Trashed = false then 1 else 0 end) as Count
精彩评论