nHibernate version:

I have a problem with some HQL in a reference system I try to create:
Select b.Headline, concat(b.Creator.Name, ' ',b.Creator.SurName), b.Content, b.ID, b.CreateDate, count( elements(b.Entries) )
from cBlogEntry b
group by b.Headline, concat(b.Creator.Name, ' ',b.Creator.SurName), b.Content, b.ID, b.CreateDate

In above query all records where b.Entries is empty will not be returned.

Mapping for the two types in the query:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="MonitCMS.model" assembly="MonitCMS">
<class
name="cBlogEntry"
table="tbBlogEntrys"
mutable="true"
>
<id name="ID" column="ID">
<generator class="identity" />
</id>
<property name="Headline" column="Headline" />
<property name="Content" column="Content" />
<property name="CreateDate" column="CreateDate" />



<many-to-one name="Creator" column="Creator" class="cUser"/>
<many-to-one name="Blog" column="BlogID" class="cBlog"/>


<bag name="Entries" table="tbBlogComments" inverse="true" lazy="true" order-by="CreateDate ASC" >
<key column="BlogEntryID"></key>
<one-to-many class="cBlogComment" />
</bag>

</class>
</hibernate-mapping>

and the other

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="MonitCMS.model" assembly="MonitCMS">
<class
name="cBlogComment"
table="tbBlogComments"
mutable="true"
>
<id name="ID" column="ID">
<generator class="identity" />
</id>
<property name="Headline" column="Headline" />
<property name="Content" column="Content" />
<property name="CreateDate" column="CreateDate" />



<many-to-one name="Creator" column="Creator" class="cUser"/>
<many-to-one name="BlogEntry" column="BlogEntryID" class="cBlogEntry"/>


</class>
</hibernate-mapping>

tai.

Hmmm, can you check the SQL statement that hibernate generates for the query ? Probably there's a inner join to Entries which causes the missing rows if there're no Entries.

Try "... from cBlogEntry b left join b.Entry". Maybe that will work as expected. You can also try and add not-found="ignore" to the one-to-many. Maybe that has an influence on the join.

Hi,

The resulting SQL is:
select
cblogentry0_.Headline as x0_0_,
(cuser1_.Name+' '+cuser1_.SurName) as x1_0_,
cblogentry0_.Content as x2_0_,
cblogentry0_.ID as x3_0_,
cblogentry0_.CreateDate as x4_0_,
count_big(entries2_.ID) as x5_0_
from
tbBlogEntrys cblogentry0_,
tbUsers cuser1_,
tbBlogComments entries2_
where
cblogentry0_.Creator=cuser1_.ID
and
cblogentry0_.ID=entries2_.BlogEntryID

No outer join operation here, explains whats wrong. Is there anyway I can force different behavior?

Have you trief "from cBlogEntry b left join b.Entry e" instead of just "from cBlogEntry b" ?

10:49:11.312 [4] DEBUG NHibernate.Engine.Query.QueryPlanCache - unable to locate HQL query plan in cache; generating (Select b.Headline, concat(b.Creator.Name, ' ',b.Creator.SurName), b.Content, b.ID, b.CreateDate, count( elements(b.Entries) )
from cBlogEntry b left join b.Entries
group by b.Headline, concat(b.Creator.Name, ' ',b.Creator.SurName), b.Content, b.ID, b.CreateDate
)

SQL:
select cblogentry0_.Headline as x0_0_, (cuser2_.Name+' '+cuser2_.SurName) as x1_0_, cblogentry0_.Content as x2_0_, cblogentry0_.ID as x3_0_, cblogentry0_.CreateDate as x4_0_, count_big(entries3_.ID) as x5_0_ from tbBlogEntrys cblogentry0_ left outer join tbBlogComments entries1_ on cblogentry0_.ID=entries1_.BlogEntryID, tbUsers cuser2_, tbBlogComments entries3_ where cblogentry0_.Creator=cuser2_.ID and cblogentry0_.ID=entries3_.BlogEntryID group by cblogentry0_.Headline , (cuser2_.Name+' '+cuser2_.SurName) , cblogentry0_.Content , cblogentry0_.ID , cblogentry0_.CreateDate

You see it is almost there, for some reason a implicit join is done on tbBlogEntries. If I remove this then everything is fine. (ie.
select
cblogentry0_.Headline as x0_0_,
(cuser2_.Name+' '+cuser2_.SurName) as x1_0_,
cblogentry0_.Content as x2_0_,
cblogentry0_.ID as x3_0_,
cblogentry0_.CreateDate as x4_0_,
count_big(entries1_.ID) as x5_0_
from
tbBlogEntrys cblogentry0_
left outer join
tbBlogComments entries1_
on
cblogentry0_.ID = entries1_.BlogEntryID,
tbUsers cuser2_
where
cblogentry0_.Creator=cuser2_.ID
group by
cblogentry0_.Headline , (cuser2_.Name+' '+cuser2_.SurName) , cblogentry0_.Content , cblogentry0_.ID , cblogentry0_.CreateDate)

Try this:


Select b.Headline, concat(b.Creator.Name, ' ',b.Creator.SurName), b.Content, b.ID, b.CreateDate, count( elements(e) )
from cBlogEntry b left join b.Entries e
group by b.Headline, concat(b.Creator.Name, ' ',b.Creator.SurName), b.Content, b.ID, b.CreateDate
)

but:
NHibernate.QueryException: could not resolve property: elements of: MonitCMS.model.cBlogComment!

Hmm ok .... then my last idea: "left join fetch b.Entries" and go back to count(elements(b.Etnries))

No luck:

select
entries1_.ID as ID6_,
entries1_.Headline as Headline6_,
entries1_.Content as Content6_,
entries1_.CreateDate as CreateDate6_,
entries1_.Creator as Creator6_,
entries1_.BlogEntryID as BlogEntr6_6_,
entries1_.BlogEntryID as BlogEntr6_0__,
entries1_.ID as ID0__,
cblogentry0_.Headline as x0_0_,
(cuser2_.Name+' '+cuser2_.SurName) as x1_0_,
cblogentry0_.Content as x2_0_,
cblogentry0_.ID as x3_0_,
cblogentry0_.CreateDate as x4_0_,
count_big(entries3_.ID) as x5_0_
from
tbBlogEntrys cblogentry0_
left outer join
tbBlogComments entries1_
on
cblogentry0_.ID=entries1_.BlogEntryID,
tbUsers cuser2_,
tbBlogComments entries3_
where
cblogentry0_.Creator=cuser2_.ID
and
cblogentry0_.ID=entries3_.BlogEntryID
group by cblogentry0_.Headline , (cuser2_.Name+' '+cuser2_.SurName) , cblogentry0_.Content , cblogentry0_.ID , cblogentry0_.CreateDate order by entries1_.CreateDate ASC

Ill try to get latest from thrunk - maybe that could solve it.