Hi,

I have a problem getting something to work. I have a collection of Registration Entities. I have to apply a filter on the getMulti of the collection that includes a MAX(RegistrationDate) and therefore also groupBy. I can't find a good example in the documentation or on this forum which can help me with this. Can someone tell me how to do this?

I have registrationdate, person_id, location in a table and I would like to get each last registration for a person for each location, so SQL will is like:

SELECT MAX(RegistrationDate) RegistrationDate, person_id, location From Registration WHERE person_id = 12 GROUP BY person_id, location ORDER BY location ASC

What I have is:

RegistrationCollection rc= new RegistrationCollection ();
PredicateExpression filter = new PredicateExpression();
filter.Add(PredicateFactory.CompareValue(RegistrationFieldIndex.person_id, ComparisonOperator.Equal, 12));
SortExpression sorter = new SortExpression();
sorter.Add(SortClauseFactory.Create(RegistrationFieldIndex.Location, SortOperator.Ascending));

rc.GetMulti(filter, 0, sorter);         

Now all I need is code to add the Max Readingdate and the groupby ...

Thanks,

G.I.

You want to fetch a list, not objects. Objects have the fields which are mapped onto tables, you want to add additional data to that, and fetch a complete different set of data, where each row doesn't represent an entity but just a new relation of fields.

You therefore should either use a typed list or a dynamic list to grab your data.

I don't want a list, I want these entities. Maybe my SQL query is wrong for this action, maybe it should containt a where clause.

But as I see it: I do a query on a single table, which means I should be able to get these rows as entities in a collection.

Maybe instead of using MAX(Readingdate) I should use a WHERE clause to set Reading date = SELECT MAX(ReadingDate) or something like that using a SELECT IN query maybe?

But how do I do that then?

As you want to read entities, you should define a filter which defines the set of entities you want to retrieve, thus with a predicate expression.

In your case, you should use a FieldCompareSetExpression, with operator SetOperator.Equal, , a specification that you want just 1 element and an order by on teh MAX field DESC. this is faster than a MAX, and gives the same results.

Please see this thread for details on how to build the query you want: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3382

Hmmm....maybe it's me, or maybe it's not, but I have tried to use the code from the other thread you mentioned and I saw this code:

IEntityField2 setField = EntityFieldFactory.Create(BlockVersionFieldIndex.Version);
setField.AggregateFunctionToApply = AggregateFunction.Max;
setField.ObjectAlias = "VC";

// bucket is not necessary here,
//RelationPredicateBucket setFilter = new RelationPredicateBucket();
IPredicate setFilter = PredicateFactory.CompareValue(BlockVersionFieldIndex.Name, ComparisonOperator.Equal, @"""GIS"".""BLOCK_VERSION"".""NAME""", "BV", "VC");

RelationPredicateBucket filter = new RelationPredicateBucket();

filter.PredicateExpression.Add(new FieldCompareSetPredicate(
    EntityFieldFactory.Create(BlockVersionFieldIndex.Version), null,
    setField, null,
    SetOperator.Equal,
    new PredicateExpression(setFilter),
    null,
    string.Empty,
    0,
    null,
    false,
    null));


EntityCollection blocks = new EntityCollection(new BlockVersionEntityFactory());
mAdapter.FetchEntityCollection(blocks, filter, 0);

Ok...I use SelfServicing, but I thought I could change it to that, however:

This line of code the IDE doesn't like, it says:

Cannot implicitly convert type 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField' to 'SD.LLBLGen.Pro.ORMSupportClasses.IEntityField2'

 IEntityField2 setField = EntityFieldFactory.Create(BlockVersionFieldIndex.Version); 

So I thought...ok, then I'll change the IEntitityField2 to IEntityField and check out what happens then, but then I have a problem with this line:

IPredicate setFilter = PredicateFactory.CompareValue(BlockVersionFieldIndex.Name, ComparisonOperator.Equal, @"""GIS"".""BLOCK_VERSION"".""NAME""", "BV", "VC");

I can't add this last "VC" since this is bool negate, so I can't do that either.

Then at last, when I changed the "VC" into false, and run the code I get a null reference exception at the getMulti of my collection....

So now I have really no idea what to do now ...

Here is my Code I tried:

            MyTableCollection mrc = new MyTableCollection();
            PredicateExpression filter = new PredicateExpression();
            filter.Add(PredicateFactory.CompareValue(MyTableFieldIndex.ObjNo, ComparisonOperator.Equal, pt.MachineObjNo));
            
            IEntityField2 setField = EntityFieldFactory.Create(MyTableFieldIndex.ReadingDate);
            setField.AggregateFunctionToApply = AggregateFunction.Max;
            setField.ObjectAlias = "MR2";
            
            IPredicate setFilter = PredicateFactory.CompareValue(MyTableFieldIndex.ReadingDate, ComparisonOperator.Equal, @"DBO.MY_TABLE.READING_DATE" , "MR2", false);
            filter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(MyTableFieldIndex.ReadingDate), null, setField, null, SetOperator.Equal, new PredicateExpression(setFilter), null, "MR1", 0, null, false, null));
                        
            mrc.GetMulti(filter, 0);

Please help me...

G.I.

First, I think it's good to have a look at the reference manual, available in the llblgen pro installation folder (and in the windows menu). In there you can look up which constructors you might have to use.

The problem you ran into is that you used an Adapter only constructor for FieldCompareSetPredicate, while you needed the selfservicing only constructor.

So change this:

lter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(MyTableFieldIndex.ReadingDate), null, setField, null, SetOperator.Equal, new PredicateExpression(setFilter), null, "MR1", 0, null, false, null));

into

lter.Add(new FieldCompareSetPredicate(EntityFieldFactory.Create(MyTableFieldIndex.ReadingDate), setField, SetOperator.Equal, new PredicateExpression(setFilter), null, "MR1", 0, null, false, null));

As you can see, you now're using the selfservicing constructor. I don't see you're sorting the subquery nor limiting the result, though you should, otherwise SetOperator.Equal fails, as your subquery should produce a single value.

Also change:

IEntityField2 setField = EntityFieldFactory.Create(MyTableFieldIndex.ReadingDate);

into:

IEntityField setField = EntityFieldFactory.Create(MyTableFieldIndex.ReadingDate);

IEntityField2 is for adapter.

I see you apply 'MAX' to the field, though you don't specify a groupby collection. I'd go for the single value and the descending sort.