Active Query Builder support area

Check if any data sources are unjoined in the query

Avatar
  • updated
  • 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;
}
Avatar
Marc

Thank you

Avatar
Andrey Zavyalov, PM

Hello, 

The code looks fine.

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

Avatar
Marc

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

   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;

        }

 

Avatar
Andrey Zavyalov, PM

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)