Using GROUP BY and HAVING clauses

This section discusses the usage of GROUP BY and HAVING clauses with typed lists, typed views and dynamic lists. Usage is the same for all three.

Using GroupByCollection and Having Clauses

Data in lists, be it a TypedView, a TypedList or a Dynamic List, is often grouped into smaller sets, which are then processed by Aggregate functions. LLBLGen Pro allows you to specify a GroupByCollection when calling a TypedList's or TypedView's Fill() method.

The GroupByCollection can contain a custom filter which will be used as a HAVING clause in the query to generate. The filter is a normal PredicateExpression and can contain any predicate you would otherwise use in a normal filter, with one restriction: fields referred to in a Having clause have to be part of the GroupByCollection or have to have an aggregate function applied to them. This is a SQL restriction. For more information about aggregate functions, please see Field expressions and aggregates.

To make effective use of a GROUP BY action, fields in the resultset should be in the GroupByCollection or have an aggregate function applied to them. You can apply aggregate functions in the TypedList editor, and also in your code. The example below applies aggregates and expressions to the fields in a TypedList called OrderTotals.

The TypedList contains just two fields: OrderDetails.OrderId** (aliased as "OrderId" at index 0) and OrderDetails.UnitPrice (aliased as "TotalPrice", as it will contain the total price of the order, at index 1).

By applying expressions, aggregates and a group by action, together with a having clause, the typed list will contain all orders with a total price higher than $1,000.=. The data also could have been read using a dynamic list, as is illustrated later in this section. By assigning the expression and aggregate function to the field object which is used in both the fields list for fetching and the having clause, the expression and aggregate are used in both the select list and the having clause.

var orderTotals = new OrderTotalsTypedList();
var groupByClause = new GroupByCollection();
// grab the fields collection. 
var fields = orderTotals.BuildResultset();    
groupByClause.Add(fields[0]);
// construct Having filter. 
// Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
groupByClause.HavingClause = new PredicateExpression(
    fields[1]
        .SetExpression(
            (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) - 
            ((OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) * OrderDetailsFields.Discount))
        .SetAggregateFunction(AggregateFunction.Sum)
    > 1000.0f);
TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, orderTotals, 0, null, null, orderTotals.BuildRelationSet(), 
    true, groupByClause, null, 0, 0);

Dim orderTotals As New OrderTotalsTypedList()
' grab the fields collection. 
Dim fields As IEntityFields = orderTotals.BuildResultset()
Dim groupByClause As IGroupByCollection = New GroupByCollection()
groupByClause.Add(fields(0))
' construct Having filter. 
' Expression for total price: ((unitprice * quantity) - ((unitprice * quantity) * discount) )
groupByClause.HavingClause = New PredicateExpression( _
    fields(1) _
        .SetExpression( _
            (OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) - _
            ((OrderDetailsFields.UnitPrice * OrderDetailsFields.Quantity) * OrderDetailsFields.Discount)) _
        .SetAggregateFunction(AggregateFunction.Sum) _
    > 1000.0)
Dim dao As New TypedListDAO()
dao.GetMultiAsDataTable(fields, orderTotals, 0, Nothing, Nothing, orderTotals.BuildRelationSet(), _
    True, groupByClause, Nothing, 0, 0)

The main part is the creation of the Expression object which will calculate the proper total for an order. As expression objects are re-usable objects, the code might look a little verbose, but can be re-used in your application. The GroupByCollection's HavingClause is set to a FieldCompareValuePredicate object which compares the TotalPrice value with a value of 1000.0.

As the field used in the predicate is the same as the field in the resultset, we get the proper expression and aggregate function applied to the field in the Having clause, as the Total price has to be re-calculated in the Having clause.