QuerySpec and nested join

Posts   
 
    
mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 29-Sep-2016 18:14:22   

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

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Sep-2016 06:51:31   

So why not using a prefetchPath to fetch the entire graph, using a sorter and a limit in the leaf edge?

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 30-Sep-2016 07:33:31   

Walaa wrote:

So why not using a prefetchPath to fetch the entire graph, using a sorter and a limit in the leaf edge?

Good thinking, that should probably do the trick.

However, I'd really like to know the queryspec solution (without prefetch), if there's any.

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 30-Sep-2016 10:00:45   

It turns out that it doesn't work, so still looking for solution.

~~Nevermind, I've solved it using a correlated query. Here (if anybody is interested) is a sample (that selects single measurement_request)

[code] var qf1 = new QueryFactory(); var q1 = qf1.MeasurementRequest .OrderBy(MeasurementRequestFields.Start.Descending()) .Limit(1) .Select(MeasurementRequestFields.Id, MeasurementRequestFields.TypeId);

var qf = new QueryFactory(); var q = qf.MeasurementType .From(QueryTarget.LeftJoin(q1.As("MTypes")).On(MeasurementTypeFields.Id == qf1.Field("MTypes", nameof(MeasurementRequestFields.TypeId)))) .Select(() => new { MeasurementTypeId = MeasurementTypeFields.Id.ToValue<int>(), RequestId = qf1.Field("MTypes", nameof(MeasurementRequestFields.Id)).As("mr_id").ToValue<int?>() }); [/code]

It is not exactly what I was looking for, but it is enough to solve it (by adding few joins here and there simple_smile )~~

mihies avatar
mihies
User
Posts: 800
Joined: 29-Jan-2006
# Posted on: 30-Sep-2016 12:50:17   

Eventually it works (its a bit more complicated that my sample but the gist is there)


var qfRequests = new QueryFactory();
var qRequests = qfRequests.MeasurementRequest
            .Where(MeasurementRequestFields.IsDeleted == false 
                    & (MeasurementRequestFields.End == DBNull.Value | MeasurementRequestFields.End < DateTime.UtcNow)
                    & MeasurementRequestFields.AdmissionId == admissionId)
            .CorrelatedOver(MeasurementRequestFields.TypeId == MeasurementTypeFields.Id)
            .OrderBy(MeasurementRequestFields.Start.Descending())
            .Limit(1)
            .Select(MeasurementRequestFields.Id);

var qfValues = new QueryFactory();
var qValues= qfValues.Measurement
                .From(QueryTarget.InnerJoin(MeasurementEntity.Relations.MeasurementRequestEntityUsingRequestId))
                .Where(MeasurementRequestFields.AdmissionId == admissionId)
                .CorrelatedOver(MeasurementRequestFields.TypeId == MeasurementTypeFields.Id)
                .OrderBy(MeasurementFields.Taken.Descending())
                .Limit(1)
                .Select(MeasurementFields.Id);

var qf = new QueryFactory();
var q = qf.MeasurementType
            .From(
                QueryTarget
                    .LeftJoin(qf.MeasurementRequest).On(MeasurementRequestFields.Id.In(qRequests))
                    .LeftJoin(qf.Measurement).On(MeasurementFields.Id.In(qValues))
)
        .Select(() => new 
                                {
            // ....
        });

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 30-Sep-2016 13:30:52   

Thanks for the feedback, though I still see the prefetchPath one is simpler simple_smile