I am having trouble creating code that will sort a collection based on a aggregate field in a child collection.

Basically i want the sql to end up something like this.

select parent.field1, parent.field2 from parent inner join child on parent.field1=child.field1 group by parent.field1, parent.field2 order by Sum(child.field2) ASC

I can't seem to get the SUM part working. This is what I've come up with so far

sorter.Add(new SortClause((DALHelper.ChildFields.field2.SetAggregateFunction(AggregateFunction.Sum)),null, llbl.SortOperator.Ascending));

Your help and wisdom is anxiously awaited.

Take a look at this post and let us know if it helps solve your problem.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2623&HighLight=1

If not post the code for what you have tried and we can help out a little more .

Thanks, Brian

Nope that doesn't help, that is the thread i used to attempt this originally.

So far I've put this together but I need to add a groupby to the statement so that the sort works properly on the aggregate.

llbl.ISortExpression sorter = new llbl.SortExpression();
sorter.Add(new llbl.SortClause((DALHelper.ExpenseClaimItemFields.Amount
     .SetAggregateFunction(llbl.AggregateFunction.Sum)), null, llbl.SortOperator.Ascending));

llbl.IRelationCollection relations = new llbl.RelationCollection();        relations.Add(DALEntity.ExpenseClaimEntity.Relations.ExpenseClaimItemEntityUsingExpenseClaimId);

llbl.IPredicateExpression filter = new llbl.PredicateExpression();
ExpenseClaims = new DALCollection.ExpenseClaimCollection();
ExpenseClaims.GetMulti(filter, 0, sorter, relations);

The SQL that this outputs has an

ORDER BY Amount ASC

statement but no corresponding Group by statment.

group by on entity fetches aren't supported, as you're fetching entity data, not grouped data.

So i can do the following

                //set the group by clause and having statement
                llbl.IGroupByCollection groupBy = new llbl.GroupByCollection();
                groupBy.Add(DALHelper.ExpenseClaimItemTemplateFields.ExpenseClaimId);
                groupBy.HavingClause = new llbl.PredicateExpression((DALHelper.ExpenseClaimItemTemplateFields.Amount
                    .SetAggregateFunction(llbl.AggregateFunction.Sum)
                    >= txtTotalFilterAmount1.Text));
                
                //set the relations for the set query
                llbl.IRelationCollection relations = new llbl.RelationCollection();
                relations.Add(DALEntity.ExpenseClaimTemplateEntity.Relations.ExpenseClaimItemTemplateEntityUsingExpenseClaimId);

                //set the subselect filter.
                amtfilter.AddWithAnd(new llbl.FieldCompareSetPredicate(
                    DALHelper.ExpenseClaimTemplateFields.ExpenseClaimId,
                    DALHelper.ExpenseClaimItemTemplateFields.ExpenseClaimId,
                    llbl.SetOperator.In, null, relations, string.Empty, 0, null, false, groupBy));

to filter using a group by, but not with sorting?? That is unfortunate.

Is it possible to do this with a Typed List? I'm getting the same issue where the sql doesn't wrap the SUM around my sort by Amount.

What would be the recommended way to accomplish this using llblgen?

I've gotten the functionality i need by adding a view in the database and mapping an entity to that view.

To recap: in v2 you can sort on an aggregate/expression. In v1 this isn't possible.