ORDER BY ON MAX(field)

Posts   
 
    
simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 25-Jun-2018 08:59:12   

OrderBy seems to ignore that fact that the defined field has Max on it.

                var loanIDQuery = qf.Create()
                    .From(Joins.Inner(LoanEntity.Relations.ItemEntityUsingItemID))
                    .Where(LoanFields.ReturnDateTime == DBNull.Value)
                    .GroupBy(ItemFields.ResourceID)
                    .OrderBy(LoanFields.IssueDateTime.SetAggregateFunction(AggregateFunction.Max) | SortOperator.Descending)
                    .Limit(50)
                    .Select(LoanFields.ID.Max());

LLBLGen v4.2

simmotech
User
Posts: 1024
Joined: 01-Feb-2006
# Posted on: 25-Jun-2018 09:17:49   

After a bit of detective work, I found this worked...

                var sortClause = LoanFields.IssueDateTime.SetAggregateFunction(AggregateFunction.Max) | SortOperator.Descending;
                sortClause.EmitAliasForExpressionAggregateField = false;

... but this is the opposite of what the docs say:

Sorting on an expression When a field with an expression is placed in a sort clause, it gives a dilemma: is this a field which is already in the select list, so an alias has to be emitted or is this an expression to use as the Order By clause? By default, LLBLGen Pro Runtime Framework will choose the former, as it's the most common, and will emit the alias set on the field instead of the expression. To sort on an expression however, the property sortclause.EmitAliasForExpressionAggregateField has to be set to true (default: false).

That property is true by default so I suspect the docs are wrong.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 25-Jun-2018 17:08:14   

Good catch! Default is indeed true, so it emits the alias, not the expression. When setting it to false it will emit the expression and not the alias.

We'll correct the docs (5.3+) (As the behavior is the intended behavior).

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 26-Jun-2018 10:03:20   

Fixed in next build.

Frans Bouma | Lead developer LLBLGen Pro