Adding Aggregate Sub Query Columns

Business Logic Framework - How To:

It is often that a developer needs to add a column/field in the select statement that is computed based on a sub query. Quick Objects Business Logic Framework introduced a new Field object that allows you to add a Aggregate SubQuery with just couple of lines of code. This sample will introduce you to the process.

In this sample we display a list of customers and for each customer we also show a sum of the OrderAmount for all orders placed by that customer.

The new class AggregateSubQueryField provides a very simple way of defining such a criteria. In this example we use an overload of the constructor to set the following properties.

BusinessObject - This instance should be the instance that will run the select command. The AggregateSubQueryField instance will automatically add itself to the Fields collection of the passed in object instance.
GroupTypes - Value indicating which aggregate function to use. None and GroupBy are not valid choices.
AggregateField - This field is used to perform the aggregate function on. For example if you want to perform "Sum" on the OrderAmount, you will pass the instance of OrderAmount field for this parameter.
ResultSetName - This value will be used as the column name in the results returned by the database. You must set this value to a non-null value and it must be unique.
LeftField and RightField - Pass the two instances of the two Field objects that the main object and sub query object have in common. For example, in this sample we pass the customer.CustomerID and o.CustomerID. If you need to set multiple, then you can add more pairs in the JoinedFields collection.

C#
VB.NET

1
// This example we will show a list of customers, and one extra column will show the Total Amount of Orders by each customer. 

2
using (Customers customer = new Customers()) 

3
{  

4
    // Aggregate Sub Query will be for OrderAmount SUM. We create a new instance of Orders. 

5
    // Note: We are not creating a Join with Order object as we don't want to include all the information from Orders table. We only want to get the total amount of all orders for each of the customers. 

6
    Orders o = new Orders(); 

7
    // Create a new instance of AggregateSubQueryField. 

8
    AggregateSubQueryField af1 = new AggregateSubQueryField(customer, GroupTypes.Sum, o.OrderAmount, "Order Total",  

9
        customer.CustomerID, o.CustomerID); 

10
 

11
    // We also want to sort by this Aggregate Sub Query column in the Descending order. 

12
    af1.Sort = SortTypes.Descending; 

13
 

14
    customer.Find(); 

15
    if (customer.AffectedRecords > 0) 

16
    { 

17
        this.dataGridView1.DataSource = customer.ResultTable; 

18
    } 

19
} 

20
 

21
 

; LINE-HEIGHT! important; BORDER-BOTTOM: rgb(127,157,185) 1px solid; FONT-FAMILY: Courier New; BACKGROUND-COLOR: white">

1	' This example we will show a list of customers, and one extra column will show the Total Amount of Orders by each customer.
2	Using customer As New Customers()
3	' Aggregate Sub Query will be for OrderAmount SUM. We create a new instance of Orders.
4	' Note: We are not creating a Join with Order object as we don't want to include all the information from Orders table. We only want to get the total amount of all orders for each of the customers.
5	Dim o As New Orders()
6	' Create a new instance of AggregateSubQueryField.
7	Dim af1 As New AggregateSubQueryField(customer, GroupTypes.Sum, o.OrderAmount, "Order Total", customer.CustomerID, o.CustomerID)
8
9	' We also want to sort by this Aggregate Sub Query column in the Descending order.
10	af1.Sort = SortTypes.Descending
11
12	customer.Find()
13	If customer.AffectedRecords > 0 Then
14	Me.dataGridView1.DataSource = customer.ResultTable
15	End If
16	End Using
17
18

Send comments on this topic.

Quick Objects Documentation
Adding Aggregate Sub Query Columns

1	// This example we will show a list of customers, and one extra column will show the Total Amount of Orders by each customer.
2	using (Customers customer = new Customers())
3	{
4	// Aggregate Sub Query will be for OrderAmount SUM. We create a new instance of Orders.
5	// Note: We are not creating a Join with Order object as we don't want to include all the information from Orders table. We only want to get the total amount of all orders for each of the customers.
6	Orders o = new Orders();
7	// Create a new instance of AggregateSubQueryField.
8	AggregateSubQueryField af1 = new AggregateSubQueryField(customer, GroupTypes.Sum, o.OrderAmount, "Order Total",
9	customer.CustomerID, o.CustomerID);
10
11	// We also want to sort by this Aggregate Sub Query column in the Descending order.
12	af1.Sort = SortTypes.Descending;
13
14	customer.Find();
15	if (customer.AffectedRecords > 0)
16	{
17	this.dataGridView1.DataSource = customer.ResultTable;
18	}
19	}
20
21