Hi Guys,

I have a query here: select p.tag_id, sum(p.stats) from main.tags t inner join main.posts p on t.id = p.tag_id group by p.tag_id order by sum(p.stats) desc

So what I basically want to achieve is get tags using order by clause like one above. Now I produced code like this:

IRelationPredicateBucket filter = new RelationPredicateBucket(); filter.Relations.Add(TagEntity.Relations.PostEntityUsingTagId, JoinHint.Inner); IGroupByCollection groupBy = new GroupByCollection(PostFields.TagId); ISortExpression sorter = new SortExpression(?????);

and I'm stuck here in SortExpression.

My question is : How can I create the sort expression which will use an aggregate function like Sum(p.stats) instead of field?

Appreciate any help, thanks, F

This is a way to achieve that using LLBLGen Runtime:

...
ISortExpression sorter = new SortExpression();

ISortClause clause = new SortClause(PostFields.Stats.SetAggregateFunction(AggregateFunction.Sum), SortOperator.Ascending);
clause.EmitAliasForExpressionAggregateField =false;
sorter.Add(clause);
...

Hi,

what would be the QuerySpec equivalent?

Task: get two fields: Id and Name ordered by aggregate SUM over Quantity

I'm trying to use:

.OrderBy(OrderDetailsFields.Quantity.Sum().Descending())

however exception

Column "XXX.OrderDetails.Quantity" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or ...

is thrown because generated query ends with

ORDER BY [Quantity] DESC

Thank you!

P.S.: LinqPad + 5.2

Hi Findev,

• Please post the complete code snippet.
• What is your LLBLGen Pro version + buildnr? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7725)

(Edit) I think I reproduced it using RTL v5.1.4:

var q = qf.Create()
    .Select(OrderFields.CustomerId, OrderFields.OrderId.Count())
    .GroupBy(OrderFields.CustomerId)
    .OrderBy(OrderFields.OrderId.Count().Descending());

produces:

...
ORDER BY [OrderId] DESC

As a workaround:

var sortClause = OrderFields.OrderId.Count().Descending();
sortClause.EmitAliasForExpressionAggregateField = false;

var q = qf.Create()
    .Select(OrderFields.CustomerId, OrderFields.OrderId.Count())
    .GroupBy(OrderFields.CustomerId)
    .OrderBy(sortClause);

produces:

...
ORDER BY COUNT([NORTHWND].[dbo].[Orders].[OrderID]) DESC

Use the workaround. I will check whether this is the expected behavior.

Please use the proposed workaround which is the current way of specifying this. There's no way in queryspec to specify it in another way. We'll look into adding a construct in a future version to make this more in line with the rest of the API (so you don't need to create the field separately).

Yes, thank you! Please post to this thread when update is in place so I refactor Thanks again!