public void AddJoinToBuilder(AggregateConfiguration user, IColumn usersExtractionIdentifier, AggregateBuilder builder, QueryBuilderArgs args) { var joinableTableAlias = args.JoinIfAny.GetJoinTableAlias(); string joinDirection = args.JoinIfAny.GetJoinDirectionSQL(); IHasRuntimeName joinOn = null; if (args.JoinedTo.Catalogue.IsApiCall(out IPluginCohortCompiler plugin)) { if (plugin == null) { throw new Exception($"No IPluginCohortCompiler was found that supports API cohort set '{args.JoinedTo}'"); } joinOn = plugin.GetJoinColumnForPatientIndexTable(args.JoinedTo); } else { joinOn = args.JoinedTo.AggregateDimensions.SingleOrDefault(d => d.IsExtractionIdentifier); } if (joinOn == null) { throw new QueryBuildingException( $"AggregateConfiguration {user} uses a join aggregate (patient index aggregate) of {args.JoinedTo} but that AggregateConfiguration does not have an IsExtractionIdentifier dimension so how are we supposed to join these tables on the patient identifier?"); } // will end up with something like this where 51 is the ID of the joinTable: // LEFT Join (***INCEPTION QUERY***)ix51 on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[patientIdentifier] = ix51.patientIdentifier builder.AddCustomLine( $" {joinDirection} Join ({Environment.NewLine}{TabIn(args.JoinSql.Sql, 1)}{Environment.NewLine}){joinableTableAlias}{Environment.NewLine}on {usersExtractionIdentifier.SelectSQL} = {joinableTableAlias}.{joinOn.GetRuntimeName()}", QueryComponent.JoinInfoJoin); }
public void AddJoinablesToBuilder(AggregateBuilder builder, AggregateConfiguration aggregate, int tabDepth) { var users = aggregate.Repository.GetAllObjectsWithParent <JoinableCohortAggregateConfigurationUse>(aggregate); foreach (var use in users) { var joinableTableAlias = use.GetJoinTableAlias(); var joinAggregate = use.JoinableCohortAggregateConfiguration.AggregateConfiguration; var identifierCol = aggregate.AggregateDimensions.Single(); var identifierColInJoinAggregate = joinAggregate.AggregateDimensions.SingleOrDefault(d => d.IsExtractionIdentifier); if (identifierColInJoinAggregate == null) { throw new QueryBuildingException("AggregateConfiguration " + aggregate + " uses a join aggregate (patient index aggregate) of " + joinAggregate + " but that AggregateConfiguration does not have an IsExtractionIdentifier dimension so how are we supposed to join these tables on the patient identifier?"); } var joinSQL = GetSQLForAggregate(joinAggregate, tabDepth + 1, true); string joinDirection = use.GetJoinDirectionSQL(); // will end up with something like this where 51 is the ID of the joinTable: // LEFT Join (***INCEPTION QUERY***)ix51 on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[patientIdentifier] = ix51.patientIdentifier builder.AddCustomLine(" " + joinDirection + " Join (" + Environment.NewLine + joinSQL + Environment.NewLine + ")" + joinableTableAlias + Environment.NewLine + "on " + identifierCol.SelectSQL + " = " + joinableTableAlias + "." + identifierColInJoinAggregate.GetRuntimeName(), QueryComponent.JoinInfoJoin); } }
public void AddJoinToBuilder(AggregateConfiguration user, IColumn usersExtractionIdentifier, AggregateBuilder builder, QueryBuilderArgs args) { var joinableTableAlias = args.JoinIfAny.GetJoinTableAlias(); string joinDirection = args.JoinIfAny.GetJoinDirectionSQL(); var joinOn = args.JoinedTo.AggregateDimensions.SingleOrDefault(d => d.IsExtractionIdentifier); if (joinOn == null) { throw new QueryBuildingException("AggregateConfiguration " + user + " uses a join aggregate (patient index aggregate) of " + args.JoinedTo + " but that AggregateConfiguration does not have an IsExtractionIdentifier dimension so how are we supposed to join these tables on the patient identifier?"); } // will end up with something like this where 51 is the ID of the joinTable: // LEFT Join (***INCEPTION QUERY***)ix51 on ["+TestDatabaseNames.Prefix+@"ScratchArea]..[BulkData].[patientIdentifier] = ix51.patientIdentifier builder.AddCustomLine(" " + joinDirection + " Join (" + Environment.NewLine + TabIn(args.JoinSql.Sql, 1) + Environment.NewLine + ")" + joinableTableAlias + Environment.NewLine + "on " + usersExtractionIdentifier.SelectSQL + " = " + joinableTableAlias + "." + joinOn.GetRuntimeName(), QueryComponent.JoinInfoJoin); }