views:

205

answers:

0

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!