NHibernate - joining on a subquery using ICriteria

Question

I have a SQL query that I need to represent using NHibernate's ICriteria API.

   SELECT u.Id as Id, 
    u.Login as Login, 
    u.FirstName as FirstName, 
    u.LastName as LastName, 
    gm.UserGroupId_FK as UserGroupId,
    inner.Data1,
    inner.Data2,
    inner.Data3
    FROM dbo.User u inner join 
    dbo.GroupMember gm on u.Id = gm.UserAnchorId_FK 
    left join
    (
        SELECT
        di.UserAnchorId_FK,
        sum(di.Data1) as Data1, 
        sum(di.Data2) as Data2, 
        sum(di.Data3) as Data3
        FROM
        dbo.DailyInfo di 
        WHERE di.Date between '2009-04-01' and '2009-06-01' 
        GROUP BY di.UserAnchorId_FK
    ) inner ON inner.UserAnchorId_FK = u.Id
    WHERE gm.UserGroupId_FK = 195

Attempts so far have included mapping 'User' and 'DailyInfo' classes (my entities) and making a DailyInfo object a property of the User object. However, how to map the foreign key relationship between them is still a mystery, ie

<one-to-one></one-to-one>

<one-to-many></one-to-many>

<generator class="foreign"><param name="property">Id</param></generator> (!)

Solutions on the web are generally to do with subqueries within a WHERE clause, however I need to left join on this subquery instead to ensure NULL values are returned for rows that do not join.

I have the feeling that I should be using a Criteria for the outer query, then forming a 'join' with a DetachedCriteria to represent the subquery?

Answer 1

As far as mapping the relationship, it sounds like you have a one-to-many relationship between User and DailyInfo:

<!-- User mapping -->
<bag name="DailyInfos" inverse="true">
    <key column="UserAnchorId_FK" />
    <one-to-many class="Namespace.To.DailyInfo, Namespace" />
</bag>

<!-- DailyInfo mapping -->
<many-to-one name="User" column="UserAnchorId_FK" />

As for the rest, I'm not entirely sure at the moment... it sounds like you may have a many-to-many between User and Group via the GroupMember table, which may be a complicating factor. Keep in mind that you can do .CreateCriteria("Association path", jointype) on a criteria to create a sub-criteria with the specified join type. Posting a rundown of your class/table relationships might help.