Using 'join' in HQL translates to two 'inner joins' in SQL

[Markus Ewald]

Hi!

I've got two tables in an n:m relationship and I want to count the
number of records that are associated with one record in the other table.
After reading an Ayende's blog that this type of relationship is not
supported by the Criteria API, I came up with the following HQL query:

long visibleDiagnosisCount = Session
.CreateQuery("select count(*) from Diagnosis d join d.Clients c
where c.Id = :clientId")
.SetParameter("clientId", clientId)
.UniqueResult<long>();

The first thing that baffled me was that this returns a 'long', not an
'int', causing an InvalidCastException in
AbstractQueryImpl.UniqueResult<T> when I used an 'int' instead. Luckily
I compiled NHibernate from source and see exactly why the exception
occurred.

Above query produces the following SQL statement:

select count(*) as col_0_0_
from Diagnoses diagnosis0_
inner join ClientDiagnoses clients1_ on
diagnosis0_.Id=clients1_.DiagnosisId
inner join Clients client2_ on clients1_.ClientId=client2_.Id
where client2_.Id=?p0

It's probably a minor issue, as there are no additional roundtrips to
the database involved, but as I have several of those kinds of queries,
I wanted to ask whether the HQL query could be improved somehow to avoid
the second 'inner join', i.e. result in this SQL statement:

select count(*) as col_0_0_
from Diagnoses diagnosis0_
inner join ClientDiagnoses clients1_ on
diagnosis0_.Id=clients1_.DiagnosisId
where clients1_.ClientId=?p0

Is that possible?

Regards,
Markus Ewald