Hi,

I have a query I would like to form where basically I want it to be equilvalent to SQL like this:

select * from tableA a join

( select MAX(AssessmentDate) as latestDate, ForeignKeyID from tableA a2 group by ForeignKeyID ) k on k.latestDate = a.AssessmentDate and k.ForeignKeyID = a.ForeignKeyID

so in plain english, i want to have a subquery that selects a single row for each unique entry in a foreign key field, and that row should be the row that has the latest entry date. Then I want to join that list of IDs into my parent query and retrieve only those rows that match a row in the subquery. From the docs I can see I should somehow use the FieldCompareSetPredicate, but I'm not 100% sure of how to achieve this.

Thanks very much, Matt

Then I want to join that list of IDs into my parent query and retrieve only those rows that match a row in the subquery.

I might be mistaken but I think the query you have posted does not match the results you want.

The query you have posted will display all the rows in TableA with an extra information (latestDate), there will be no filtering the TableA rows.

If Table A (ID, FKID, Date) has the following rows:

1 1 1/1/2005 2 1 1/1/2006 3 1 1/1/2004 4 2 1/1/2002 5 2 1/1/2001

Then the results would be:

1 1 1/1/2005 1/1/2006 2 1 1/1/2006 1/1/2006 3 1 1/1/2004 1/1/2006 4 2 1/1/2002 1/1/2002 5 2 1/1/2001 1/1/2002

If I'm correct please re-post the modified query.

Hi Walaa,

Thanks for the reply. The query does work I've run it against my data and just changed the column names. In the example you have posted it would only return the two rows:

2 1 1/1/2006 4 2 1/1/2002

this part of the query: select MAX(AssessmentDate) as latestDate, ForeignKeyID from tableA a2 group by ForeignKeyID

would return 1/1/2006, 1 1/1/2002, 2

then the outer join only joins to rows that have the same date and the same ForeignKeyID in this part: k on k.latestDate = a.AssessmentDate **and ** k.ForeignKeyID = a.ForeignKeyID

so the extra rows you list as being returned would be disqualified because their date doesn't match the date calculated in the inner query.

I have actually since read that it's not possible to handle joins to inner selects in LLBLGen, is this still the case do you know? I might have to either figure out a way to rewrite it, or do some manually filtering in code I guess if that is the case.

Thanks again, Matt

so the extra rows you list as being returned would be disqualified because their date doesn't match the date calculated in the inner query.

Right you are, I didn't see the joining on the date.

I guess your query could be re-written as

Select * from tableA a
Where AssessmentDate IN
(
select MAX(AssessmentDate) as AssessmentDate
From tableA 
Where ForeignKeyID = a.ForeignKeyID
Group by ForeignKeyID
)

And then you may use FieldCompareSetPredicate to get the IN clause. Please refer to the LLBLGen Pro manual "Using the generated code -> Adapter/SelfServicing -> Filtering and sorting -> The predicate system"

Also check the samples in the following threads: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3401 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3382 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3795 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4122 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5487

Thanks again Walaa.

Hi again, So I've had a go at this, but am struggling a little to map it into LLBLGen predicates and such.

This is the complete query (with variables and select * for clarity):

select * from Assessment a where

assessmentdate in

( select MAX(AssessmentDate) as latestDate from Assessment a2 Join Metric m on a2.MetricID = m.MetricID where a2.MetricID = a.MetricID and m.SpecialityID = @SpecialityID group by a2.MetricID ) and userID = @UserID

and this is what I have in code:

IPredicateExpression subFilter = new PredicateExpression(PredicateFactory.CompareValue(AssessmentFieldIndex.MetricId, ComparisonOperator.Equal, AssessmentFieldIndex.MetricId, "A2")) .AddWithAnd(PredicateFactory.CompareValue(MetricFieldIndex.SpecialityId, ComparisonOperator.Equal, specialityID)); IRelationCollection relations = new RelationCollection(); relations.Add(MetricEntity.Relations.AssessmentEntityUsingMetricId, "A2");

        IGroupByCollection groupBy = new GroupByCollection();
        groupBy.Add(AssessmentFields.MetricId);

        RelationPredicateBucket filter = new RelationPredicateBucket();         
        filter.PredicateExpression.Add(                         
                new FieldCompareSetPredicate(AssessmentFields.AssessmentDate,
                        null,
                        AssessmentFields.AssessmentDate,
                        null,
                        SetOperator.In,
                        subFilter,
                        relations,
                        "A2",
                        100, //change this try -1 to get it to select all
                        null,
                        false,
                        groupBy))
            .AddWithAnd(AssessmentFields.UserId == userID);

any ideas on what's going wrong?

Thanks, Matt

Try the following:

IPredicateExpression subQuery = new PredicateExpression();
subQuery.Add(PredicateFactory.CompareExpression(AssessmentFieldIndex.MetricID, ComparisonOperator.Equal, new Expression(EntityFieldFactory.Create(AssessmentFieldIndex.MetricID)), "A2"));
subQuery.Add(PredicateFactory.CompareValue(MetricFieldIndex.SpecialityID, ComparisonOperator.LessEqual, specialityID));

IRelationCollection relations = new RelationCollection();
relations.Add(MetricEntity.Relations.AssessmentEntityUsingMetricId, "A2");

IEntityField2 field1 = EntityFieldFactory.Create(AssessmentFieldIndex.AssessmentDate);
IEntityField2 field2 = EntityFieldFactory.Create(AssessmentFieldIndex.AssessmentDate);
field2.ObjectAlias = "A2";
field2.AggregateFunctionToApply = AggregateFunction.Max;

IPredicate predicate1 = new FieldCompareSetPredicate(field1, null, field2, null, SetOperator.Equal, subQuery);

RelationPredicateBucket filter = new RelationPredicateBucket(); 
filter.PredicateExpression.Add( 
new FieldCompareSetPredicate(field1,
null,
field12,
null,
SetOperator.In,
subQuery,
relations,
"A2", // maybe you don't need this
100,
null,
false,
groupBy))
.AddWithAnd(AssessmentFields.UserId == userID);

You may examine the generated query and tweek it a little more or see what's going wrong.

Please refer to the LLBLGen Pro documentation manual "Using the generated code -> Troubleshooting and debugging"

Brilliant Walaa thanks once again A couple of tweaks and it worked a treat.