NHibernate HQL - select count(*) with having - can't get it to work

Question

Trying to run the following HQL with NHibernate:

select count(distinct t) as TweetCount
from Tweet t
    join t.Tweeter u
    left join t.Votes v
    left join t.Tags tag
where t.App = :app
having count(distinct v) > 0

But for some reason the having clause is being ignored and it's counting all tweets when only 2 tweets have a vote. I basically want to count the number of Tweets that have at least one Vote.

Here is my database

I tried adding a group by to my query like so:

select count(distinct t) as TweetCount
from Tweet t
    join t.Tweeter u
    left join t.Votes v
    left join t.Tags tag
where t.App = :app
group by t
having count(distinct v) > 0

...but it ended up returning a collection containing 2 integers each set to '1' instead of a unique result.

Answer 1

this will fit thwe bill

select count(distinct t.Id) as TweetCount
from Tweet t
    inner join t.Votes v
where t.App = :app

since we are inner joining the Votes table, any Tweet that has no votes will not be counted against the result set.

An other way using pure HQL syntax would be

select count(distinct t.Id) as TweetCount
from Tweet t
where t.App = :app and size(t.Votes) > 0

which will create an sql statement depending on your dialect, the size() function is hql-specific for collections, see 13.8 from the NHibernate reference