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?
