I have the following two tables mapped in NHibernate:
VehicleCheck
ID (int)
VehicleReg (varchar)
DriverName (varchar)
TS (datetime)
VehicleCheckItem
ID (int)
VehicleCheckID (int)
CheckType (int)
Passed (bit)
What I want to do is get the most recent vehicleCheck for each vehicleReg and a count of vehicle checks failed for each, using HQL or the Criteria API.
In SQL, this would look like:
select VehicleCheck.ID, VehicleCheck.VehicleReg, VehicleCheck.DriverName,
VehicleCheck.TS, CheckItems.FailCount
FROM VehicleCheck
JOIN
(select VehicleReg, Max(TS) TS
FROM VehicleCheck
GROUP BY VehicleReg) LastChecks
ON LastChecks.VehicleReg = VehicleCheck.VehicleReg and LastChecks.TS = VehicleCheck.TS
LEFT OUTER JOIN
(select VehicleCheckID, Count(Passed) FailCount
FROM VehicleCheckItem
Where Passed = 0
GROUP BY VehicleCheckID) CheckItems
on CheckItems.VehicleCheckID = VehicleCheck.ID
Which gives me ID, VehicleReg, DriverName, TS and FailCount for the most recent vehicle check by Vehicle Reg.
How would I go about doing that in HQL? Does HQL Support subqueries in joins?
I got as far as:
select vehicleCheck.ID, vehicleCheck.DriverName, vehicleCheck.VehicleReg, max(vehicleCheck.TS),
count(vehicleCheckItems.Passed)
from VehicleCheck vehicleCheck
join vehicleCheck.VehicleCheckItems vehicleCheckItmes
group by vehicleCheck.VehicleReg
Which gives me almost the right results, but the group by VehicleReg causes the count of VehicleCheckItems to be per Reg rather than per check if that makes sense.
Thanks!