Hi guys,
This is a from part of my query
.From(qf.MeasurementType
.LeftJoin(MeasurementTypeEntity.Relations.MeasurementRequestEntityUsingTypeId)
.LeftJoin(qf.Measurement).On(
(MeasurementRequestFields.Id == MeasurementFields.RequestId)
.And(MeasurementFields.Id.In(
qf.Measurement.Select(MeasurementFields.Id)
.OrderBy(MeasurementFields.Taken.Descending()).Limit(1))
))
);
Entities are master detail (MeasurementType->MeasurementRequest->Measurement).
I want to fetch all types with latest measurements.
For that I need to inner join the most nested query, from
qf.Measurement.Select(MeasurementFields.Id)
.OrderBy(MeasurementFields.Taken.Descending()).Limit(1)
to (bold are what I'm after)
qf.Measurement.InnerJoin(MeasurementRequest).Select(MeasurementFields.Id)
.Where(MeasurementRequest.Id == outer MeasurementRequest.Id)
.OrderBy(MeasurementFields.Taken.Descending()).Limit(1)
Of course, like that it won't work (bold parts are just illustrations).
However I have some problems to achieve it.
Here is a SQL hand written query that I'm writting
select * from measurement_type
left join measurement_request on measurement_type.id = measurement_request.type_id
left join measurement on measurement_request.id = measurement.request_id
and measurement.id in (
select m.id from measurement m inner join measurement_request mr on m.request_id = mr.id
where mr.id = measurement_request.id
order by m.taken desc
limit 1
)
Thanks