How to change sorting, add filters, limits and aggregations to the query?

Last modified: 3 years ago

There's a special component intended to change SQL queries according to the end-user data browsing needs: QueryTransformer. It changes the given query to transfigure data while browsing the query result set in the grid. The component lets programmers alter sorting and filtration while browsing the query result data in the grid, apply limits to the result set, calculate totals, and add grouping by columns.

Resultset modification rules

The component modifies the query with an intention to perform any actions with the resultset of the given query. This means that the result query dataset will always be a subset of the initial query resultset. There is no way to modify a query in such a way as to get any data that goes beyond the dataset of the original query.

Applying sorting, filtration, and row limits preserve the list of columns of the original query. You can hide some columns from the list using the QueryTransformer setting the column.Visible property to false.

Applying grouping or aggregation (using the QueryTransformer.Select* methods or filling the Aggregations collection) you replace the list of initial columns with the list of aggregations. You can pass a column, an expression, or an AggregatedCoumn object to the Aggregations.Add method. To restore the original list of columns, clear the Aggregations collection.

Setting up

To set up the QueryTransformer object you must initialize its QueryProvider property with a visual QueryBuilder component or non-visual SQLQuery object. This will be the source for the initial query to be transformed. On changing the initial query, the transformed query will be changed instantly. Of course, if the new initial query doesn't contain some columns to which the modifications are applied, the corresponding modifications will be void.

Examples

Follow the simple steps below to change the query the way you need:

Find the column you need in the QueryTransformer.Columns collection:

    column = queryTransformer.Columns.ColumnByName("OrderDate"); // getting column reference by name

Create an object that defines the necessary modification:

    sorting = column.Ascending(); // ascending sort by a column
    filter = column.NotLess("31/12/2012"); // condition 'column > "31/12/2012" 
    aggregate = column.Max("DateMax"); // calculation of maximum value for a column

Apply these objects to the query:

    queryTransformer.OrderBy(sorting); // applying sorting
    queryTransformer.Where(filter); // applying filtration
    queryTransformer.Select(aggregate); // applying aggregation

Rename or hide columns:

    column.PreassignedName = "New column name";
    column.Visible = False;

Apply limits to the query:

    if (queryTransformer.IsSupportLimitCount()) {
        queryTransformer.Take("50");
    }
    if (queryTransformer.IsSupportLimitOffset()) {
        queryTransformer.Skip("100");
    }

Get the modified query from the QueryTransformer.SQL property:

    MessageBox.Show(queryTransformer.SQL);

OR save the component state for future use with the QueryTransformer.StateXML property:

    savedState = queryTransformer.StateXML;
    queryBuilder.SQL = updatedSql;
    queryTransformer.StateXML = savedState;

Read the QueryTransformer and accompanying classes property reference for details.

Below are the object relationship diagram and code samples for different languages.

Code samples

using (QueryTransformer queryTransformer = new QueryTransformer())
{
    queryTransformer.QueryProvider = queryBuilder1; // an object implementing the IQueryProvider interface
    // you can also use the non-visual SQLQuery object as a source of initial SQL query.
    queryTransformer.BeginUpdate();
 
    try
    {
        // filter
        queryTransformer.Filters.Clear();
 
        queryTransformer
            .Where(queryTransformer.Columns[1].Not_Equal("100"))
            .Where(queryTransformer.Columns[2].In("('Value 1','Value 2','Value 3')"))
            .Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100"))
            .Where("OrderDate is not null")
            .Where(FilterFactory.Or()
                .Add(queryTransformer.Columns[1].Not_Equal("100"))
                .Add(queryTransformer.Columns[1].Not_Equal("200")));
 
        // ordering
        queryTransformer.Sortings.Clear();
 
        queryTransformer
            .OrderBy(queryTransformer.Columns[1])
            .OrderBy(queryTransformer.Columns[2], false)
            .OrderBy(queryTransformer.Columns.GetColumnByName("OrderId"))
            .OrderBy("OrderDate");
 
        // limit, offset
        queryTransformer
            .Skip("100")
            .Take("50");
 
        // aggregations
        queryTransformer.Aggregations.Clear();
 
        queryTransformer
            .SelectRecordsCount("recordsCount")
            .Select(queryTransformer.Columns[1].Count(), "column1Count")
            .Select(queryTransformer.Columns[1].Sum(), "column1Sum")
            .Select(queryTransformer.Columns[1].Avg(), "column1Avg")
            .Select(queryTransformer.Columns[1].Min(), "column1Min")
            .Select(queryTransformer.Columns[1].Max(), "column1Max");
    }
    finally
    {
        queryTransformer.EndUpdate();
    }
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql);
}

Using queryTransformer As New QueryTransformer()
    queryTransformer.QueryProvider = queryBuilder1 ' an object implementing the IQueryProvider interface
    ' you can also use the non-visual SQLQuery object as a source of initial SQL query.
    queryTransformer.BeginUpdate()
 
    Try
        ' filter
        queryTransformer.Filters.Clear()
 
        queryTransformer.Where(queryTransformer.Columns(1).Not_Equal("100")). _
         Where(queryTransformer.Columns(2).[In]("('Value 1','Value 2','Value 3')")). _
         Where(queryTransformer.Columns.GetColumnByName("OrderId").Condition("> 100")). _
         Where("OrderDate is not null"). _
         Where(FilterFactory.[Or](). _
          Add(queryTransformer.Columns(1).Not_Equal("100")). _
           Add(queryTransformer.Columns(1).Not_Equal("200")))
 
        ' ordering
        queryTransformer.Sortings.Clear()
 
        queryTransformer. _
         OrderBy(queryTransformer.Columns(1)). _
         OrderBy(queryTransformer.Columns(2), False). _
         OrderBy(queryTransformer.Columns.GetColumnByName("OrderId")). _
         OrderBy("OrderDate")
 
        ' offset
        queryTransformer. _
         Skip("100"). _
         Take("50")
 
        ' aggregations
        queryTransformer.Aggregations.Clear()
 
        queryTransformer. _
         SelectRecordsCount("recordsCount"). _
         Select(queryTransformer.Columns(1).Count(), "column1Count"). _
         Select(queryTransformer.Columns(1).Sum(), "column1Sum"). _
         Select(queryTransformer.Columns(1).Avg(), "column1Avg"). _
         Select(queryTransformer.Columns(1).Min(), "column1Min"). _
         Select(queryTransformer.Columns(1).Max(), "column1Max")
 
    Finally
        queryTransformer.EndUpdate()
    End Try
 
    MessageBox.Show("Modified SQL", queryTransformer.Sql)
End Using

queryTransform.BeginUpdate;
try

       // filter
       queryTransform.Filters.Clear; // gets back the WHERE clause to initial state 

       queryTransform //adds conditions to the WHERE clause, conjunctioning it with conditions from initial query
           .Where(queryTransform.Columns[1].Not_Equal('100'))
           .Where(queryTransform.Columns[2].In_('(''Value1'',''Value2'',''Value3'')'))
           .Where(queryTransform.Columns.ColumnByName('OrderId').Condition('> 100'))
           .Where('OrderDate is not null')
           .Where(TacFilterFactory.Or_()
               .Add(queryTransform.Columns[1].Not_Equal('100'))
               .Add(queryTransform.Columns[1].Not_Equal('200')));

       // ordering
       queryTransform.Sortings.Clear; // gets back the ORDER BY clause to initial state 

       queryTransform // removes the ORDER BY clause of initial query and defines alternate ordering for a query
           .OrderBy(queryTransform.Columns[1])
           .OrderBy(queryTransform.Columns[2], false)
           .OrderBy(queryTransform.Columns.ColumnByName('OrderId'))
           .OrderBy('OrderDate');

       // offset
       queryTransform // applies the offset clauses to the query according to specific database server SQL syntax
           .Skip('100')
           .Take('50');

    // aggregations
    queryTransform.Aggregations.Clear;  // removes wrapping of the query in a sub-query

    queryTransform  // wraps the query in a sub-query and defines output columns for the outer query
    .SelectRecordsCount('recordsCount')
    .Select(queryTransform.Columns[1].Count, 'column1Count')
    .Select(queryTransform.Columns[1].Sum, 'column1Sum')
    .Select(queryTransform.Columns[1].Avg, 'column1Avg')
    .Select(queryTransform.Columns[1].Min, 'column1Min')
    .Select(queryTransform.Columns[1].Max, 'column1Max')
    ;

finally
       queryTransform.EndUpdate;
end;

Private Sub miTransform_Click()
Dim t As ActiveQueryBuilderXControls.aqbxQueryTransformer
Set t = ActiveQueryBuilderX1.QueryTransformer

t.BeginUpdate
On Error GoTo EndUpdate

' prepare
Dim column0 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column0 = t.Columns.Items(0)
Dim column1 As ActiveQueryBuilderXControls.aqbxOutputColumn
Set column1 = t.Columns.ColumnByName("OrderDate")


' Filtering
t.Filters.Clear

t.Where column0.Not_Equal(100)
t.Where column1.In("('Value 1','Value 2','Value 3')")
t.WhereExpr "CustomerName Like 'John%'"


' Sorting
t.Sortings.Clear

t.OrderBy column0.Ascending
t.OrderByColumn column1, False
t.OrderByExpr "CustomerName"


' limits
t.Take 50
t.Skip 100


' aggregations
t.Aggregations.Clear

t.SelectRecordsCount "recordsCount"
t.Select column0.Count, "column0Count"

EndUpdate:
t.EndUpdate

MsgBox t.SQL
End Sub

Active Query Builder support area

How to change sorting, add filters, limits and aggregations to the query?

Resultset modification rules

Setting up

Examples

Code samples