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

Oldest first
Newest first Oldest first

Andrey Zavyalov, PM

7 years ago

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)

Reply Link

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

Hello,

The code looks fine.

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

Thank you