NHibernate 2.1.0: HQL With clause

A simple SQL:

SELECT * FROM Animal AS a1

INNER JOIN Animal AS a2 on a1.mother_id = a2.ID and a1.body_weight < :aLimit

Another:

SELECT * FROM Animal AS a1 INNER JOIN Animal AS a2 on a1.mother_id = a2.ID

WHERE a1.body_weight < :aLimit

Which is the difference in term of results ? There isn’t.

Some DB engine does not make difference, between the two SQLs, even in term of execution plan but… you know, there are some RDBMS not so smart (guess which one ;-) ).

With the new HQL parser, based on ANTLR, we are supporting a new clause named “with”.

The HQL:

from Animal a inner join a.mother as m with m.bodyWeight < :someLimit

The result SQL using this mapping :

select
animal0_.id as id14_0_,
animal1_.id as id14_1_,
animal0_.description as descript2_14_0_,
animal0_.body_weight as body3_14_0_,
animal0_.mother_id as mother4_14_0_,
animal0_.father_id as father5_14_0_,
animal0_.zoo_id as zoo6_14_0_,
animal0_.serialNumber as serialNu7_14_0_,
animal0_1_.bodyTemperature as bodyTemp2_15_0_,
animal0_3_.pregnant as pregnant17_0_,
animal0_3_.birthdate as birthdate17_0_,
animal0_4_.owner as owner18_0_,
animal0_7_.name_first as name2_21_0_,
animal0_7_.name_initial as name3_21_0_,
animal0_7_.name_last as name4_21_0_,
animal0_7_.nickName as nickName21_0_,
animal0_7_.height as height21_0_,
animal0_7_.intValue as intValue21_0_,
animal0_7_.floatValue as floatValue21_0_,
animal0_7_.bigDecimalValue as bigDecim9_21_0_,
animal0_7_.bigIntegerValue as bigInte10_21_0_,
case
when animal0_2_.reptile is not null then 2
when animal0_5_.mammal is not null then 5
when animal0_6_.mammal is not null then 6
when animal0_4_.mammal is not null then 4
when animal0_7_.mammal is not null then 7
when animal0_1_.animal is not null then 1
when animal0_3_.animal is not null then 3
when animal0_.id is not null then 0
end as clazz_0_,
animal1_.description as descript2_14_1_,
animal1_.body_weight as body3_14_1_,
animal1_.mother_id as mother4_14_1_,
animal1_.father_id as father5_14_1_,
animal1_.zoo_id as zoo6_14_1_,
animal1_.serialNumber as serialNu7_14_1_,
animal1_1_.bodyTemperature as bodyTemp2_15_1_,
animal1_3_.pregnant as pregnant17_1_,
animal1_3_.birthdate as birthdate17_1_,
animal1_4_.owner as owner18_1_,
animal1_7_.name_first as name2_21_1_,
animal1_7_.name_initial as name3_21_1_,
animal1_7_.name_last as name4_21_1_,
animal1_7_.nickName as nickName21_1_,
animal1_7_.height as height21_1_,
animal1_7_.intValue as intValue21_1_,
animal1_7_.floatValue as floatValue21_1_,
animal1_7_.bigDecimalValue as bigDecim9_21_1_,
animal1_7_.bigIntegerValue as bigInte10_21_1_,
case
when animal1_2_.reptile is not null then 2
when animal1_5_.mammal is not null then 5
when animal1_6_.mammal is not null then 6
when animal1_4_.mammal is not null then 4
when animal1_7_.mammal is not null then 7
when animal1_1_.animal is not null then 1
when animal1_3_.animal is not null then 3
when animal1_.id is not null then 0
end as clazz_1_
from
Animal animal0_
left outer join
Reptile animal0_1_
on animal0_.id=animal0_1_.animal
left outer join
Lizard animal0_2_
on animal0_.id=animal0_2_.reptile
left outer join
Mammal animal0_3_
on animal0_.id=animal0_3_.animal
left outer join
DomesticAnimal animal0_4_
on animal0_.id=animal0_4_.mammal
left outer join
Cat animal0_5_
on animal0_.id=animal0_5_.mammal
left outer join
Dog animal0_6_
on animal0_.id=animal0_6_.mammal
left outer join
Human animal0_7_
on animal0_.id=animal0_7_.mammal
inner join
Animal animal1_
on animal0_.mother_id=animal1_.id
and (
animal1_.body_weight<@p0
)
left outer join
Reptile animal1_1_
on animal1_.id=animal1_1_.animal
left outer join
Lizard animal1_2_
on animal1_.id=animal1_2_.reptile
left outer join
Mammal animal1_3_
on animal1_.id=animal1_3_.animal
left outer join
DomesticAnimal animal1_4_
on animal1_.id=animal1_4_.mammal
left outer join
Cat animal1_5_
on animal1_.id=animal1_5_.mammal
left outer join
Dog animal1_6_
on animal1_.id=animal1_6_.mammal
left outer join
Human animal1_7_
on animal1_.id=animal1_7_.mammal;
@p0 = 1

First… easy HQL with complex mapping mean complex SQL but… not a big pain for NH’s users ;-)

Note this:

inner join
Animal animal1_
on animal0_.mother_id=animal1_.id
and (
animal1_.body_weight<@p0
)

stock_stack……stumb…sfrfrfrfrfrfr (biglia, sponda e filotto).