HomeActive Query Builder - Query analysis and modificationProgrammatic modification of SQL queriesHow to change sorting, add filters, limits and aggregations to the query?

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

There's a special component intended to change SQL queries according to the end-user data browsing needs: QueryTransformer. It was made to meet the needs of programmers who want to let the end-users to transfigure data while browsing query result set in the grid. The component lets change data sorting and filtration while browsing the query result data in the grid, apply limits to the result set, and calcluate totals by columns.

It needs to be linked to the QueryBuilder and SQLBuilder objects to get the initial query columns and produce the modified query text in result.

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

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

        column = queryTransformer.Columns.ColumnByName("OrderDate")
  2. Create an object that defines the necessary modification:

        sorting = column.Ascending()
        filter = column.NotLess("31/12/2012")
        aggregate = column.Max("DateMax")
  3. Apply these objects to the query:

        queryTransformer.OrderBy(sorting)
        queryTransformer.Where(filter)
        queryTransformer.Select(aggregate)
  4. Apply limits to the query:

        If queryTransformer.IsSupportLimitCount() Then
            queryTransformer.Take("50")
        End If
  5. 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 reference for details.

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

Delphi, VCL Edition

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;

C#, .NET Edition

using (QueryTransformer queryTransformer = new QueryTransformer())
{
    queryTransformer.QueryBuilder = queryBuilder1;
    queryTransformer.SqlBuilder = plainTextSQLBuilder1;
 
    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);
}

VB.NET, .NET Edition

Using queryTransformer As New QueryTransformer()
    queryTransformer.QueryBuilder = queryBuilder1
    queryTransformer.SqlBuilder = plainTextSQLBuilder1
 
    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

Visual Basic, ActiveX Edition

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

C#, ActiveX Edition

This page was: Helpful | Not Helpful