Analyzing complex SQL query structure (with unions and sub-queries)
Last modified:
Each query or subquery in the query is represented by the SubQuery object in Active Query Builder; The main query can be accessed via the QueryBuilder.SqlQuery.QueryRoot property.
Each sub-query may be either a single SELECT statement or a set of SELECT statements joined with the UNION, EXCEPT, INTERSECT and other keywords. Every single SELECT statement is represented via the UnionSubQuery object.
The UnionSubQuery object holds datasources, their links (UnionSubQuery.FromClause), expressions and criteria (UnionSubQuery.QueryColumnList) of the single SELECT statement.
There are few useful helpers you can use in your project:
- The SubQuery.FirstSelect() method returns link to the UnionSubQuery object representing the first single SELECT statement in the query.
- The QueryBuilder.ActiveUnionSubQuery property gets the single SELECT statement currently visible to the end-user. The QueryBuilder.ActiveUnionSubQueryChanged event is fired when the user switches to another sub-query.
The following code collects all SubQuery and UnionSubQuery objects in a query:
// collect all Sub-queries var allSubQueries = queryBuilder1.SQLQuery.QueryRoot.GetChildrenRecursive<SubQuery>(true); // insert the main Query to the beginning of the list allSubQueries.Insert(0, queryBuilder1.SQLQuery.QueryRoot); // collect all Union Sub-queries var allUnionSubQueries = queryBuilder1.SQLQuery.QueryRoot.GetChildrenRecursive<UnionSubQuery>(true);
// collect unions of the main query only
var unionSubQueries = queryBuilder1.SQLQuery.QueryRoot.GetChildrenRecursive<UnionSubQuery>(false);
Each query or subquery in the query is represented by the SubQuery object in Active Query Builder; The main query representation class Query is a descendant of the SubQuery class. The main query can be accessed via the QueryBuilder.Query property. To get access to the list of sub-queries, use the QueryBuilder.SubQueries collection.
Each sub-query may be either a single SELECT statement or a set of SELECT statements joined with the UNION, EXCEPT, INTERSECT and other keywords. Every single SELECT statement is represented via the UnionSubQuery object.
There are few useful helpers you can use in your project:
- The QueryBuilder.Query.FirstSelect() method returns the link to an object representing the first single SELECT statement in the query.
- The QueryBuilder.ActiveSubQuery.ActiveUnionSubQuery property gets the single SELECT statement currently visible to the end-user. The QueryBuilder.ActiveUnionSubQueryChanged event is fired when the user switches to another sub-query.
The UnionSubQuery object holds datasources, their links (UnionSubQuery.FromClause), expressions and criteria (UnionSubQuery.QueryColumnList) of the single SELECT statement.
The following code collects all SubQuery and UnionSubQuery objects in a query:
// collect all Sub-queries var allSubQueries = queryBuilder1.Query.GetChildrenRecursive<SubQuery>(true); // insert the main Query to the beginning of the list allSubQueries.Insert(0, queryBuilder1.Query); // collect all Union Sub-queries var allUnionSubQueries = queryBuilder1.Query.GetChildrenRecursive<UnionSubQuery>(true);
// collect unions of the main query only
var unionSubQueries = queryBuilder1.Query.GetChildrenRecursive<UnionSubQuery>(false);
The Query Structure Demo project that is shipped together with the full and trial installation packages provides sample code for analyzing query structure and dumping it into a text file.