Active Query Builder support area

Check if any data sources are unjoined in the query

Marc

7 years ago
updated 7 years ago
Completed

When there are 2 or more tables in a query, I want to check if any of them are unjoined. This is with the ASP.NET edition

Then is there an easier way to do this?

private bool queryHasUnjoinedDataSources() {
     var dataSources = new List<DataSource>();
         QueryBuilderControl1.QueryBuilder.ActiveSubQuery.ActiveUnionSubquery.FromClause.GetDatasources(dataSources);

//if there are more than 1 table/view in the query, make sure it is joined to the other data sources
if (dataSources.Count > 1) {
    var links = new ArrayList();
    dataSources[0].Query.FromClause.GetLinksRecursive(links);
    if (links.Count == 0) {
        return true;
    }
    var linkedDataSources = new System.Collections.Generic.HashSet<DataSource>();

    foreach (Link link in links) {
        linkedDataSources.Add(link.LeftDatasource);
        linkedDataSources.Add(link.RightDatasource);
    }

    foreach (DataSource dataSource in dataSources) {
        if (!linkedDataSources.Contains(dataSource)) {
            return true;
        }
    }
}

    //if we got here, all of the dataSources have joins
    return false;
}

private bool queryHasUnjoinedDataSources() {

            // collect all Union Sub-queries
            var allUnionSubQueries = QueryBuilderControl1.QueryBuilder.Query.GetChildrenRecursive<UnionSubQuery>(true);

            foreach (UnionSubQuery usq in allUnionSubQueries) {

                var dataSources = new List<DataSource>();
                usq.FromClause.GetDatasources(dataSources);

                 if (dataSources.Count > 1) {
                    //get the linked data sources for this usq
                    var links = new ArrayList();
                   usq.FromClause.GetLinksRecursive(links);

var linkedDataSources = new System.Collections.Generic.HashSet<DataSource>();
                    foreach (Link link in links) {
                        linkedDataSources.Add(link.LeftDatasource);
                        linkedDataSources.Add(link.RightDatasource);
                    }

    foreach (DataSource dataSource in allDataSources) {
                    if (!linkedDataSources.Contains(dataSource)) {
                         return true;
                     }
                 }
                }
            }

             //if we got here, all of the dataSources have joins
            return false;

        }

Replies 4

Newest first
Newest first Oldest first

7 years ago

Thank you

Reply Link

Andrey Zavyalov, PM

Hello,

The code looks fine.

One notice: the "dataSources[0].Query" can be replaced with "usq".

Thanks. Here's my revised function. Does this look OK to you?

Hello, Marc.

Your code looks just fine.

The only point is that it checks only the active UnionSubQuery object while the query may have unions and sub-queries. You must iterate through all of them to check the whole query. This article has samples of collecting all union sub-queries for the v.2 (on which the current ASP.NET version is based) and v.3: Analyzing complex SQL query structure (with unions and sub-queries)