public virtual string GetDataJoinTable(ReportColumnMapping groupByColumn, List <TableRelationship> statsRelationships, TableRelationshipGraphBuilder graphBuilder) { var statsJoinTables = statsRelationships .Select(x => x.Table1 is StatsTableMapping ? x.Table2.KnownTableName : x.Table1.KnownTableName) .ToList(); // find the nearest stats table to the group by table. var graph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), groupByColumn.KnownTable); graphBuilder.TrimToTables(graph, statsJoinTables); var dataTables = graphBuilder.GetAllTables(graph).Distinct().ToList(); dataTables = dataTables.OrderBy(x => graphBuilder.GetDistance(graph, x)).ToList(); var joinTable = dataTables.FirstOrDefault(statsJoinTables.Contains); return(joinTable); }
public virtual void BuildFrom( SelectQuery query, List <ReportColumnMapping> selectedColumns, ReportColumnMapping groupByColumn, ReportColumnMapping sortByColumn, MappedSearchRequest request) { var tables = request.SelectedAndDependantColumns.Select(x => x.KnownTable).ToList(); tables.Add(groupByColumn.KnownTable); tables = tables.Distinct().ToList(); var currencyColumn = GetCurrencyColumn(); if (currencyColumn != null && !tables.Contains(currencyColumn.KnownTable)) { tables.Add(currencyColumn.KnownTable); } var rootTableMapping = _tableMappings.GetTableMapping(_constants.RootTableName); query.From(rootTableMapping.DbTableName, rootTableMapping.Alias); var graphBuilder = new TableRelationshipGraphBuilder(); // bulild a graph of the table relationships to calculate the distance from the root table // include missing tables needed for joins // order the tables by distance var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName); graphBuilder.TrimToTables(relationshipGraph, tables); tables = graphBuilder.GetAllTables(relationshipGraph).Union(tables).Distinct().ToList(); tables = tables.OrderBy(x => graphBuilder.GetDistance(relationshipGraph, x)).ToList(); // dictionary of TableName, IsAggregated for tables which have been added to the query var addedTables = new Dictionary <string, bool> { { _constants.RootTableName, false } }; foreach (var table in tables.Distinct()) { AddFromTableJoin(query, groupByColumn, table, ref addedTables, graphBuilder, relationshipGraph); } }
protected void AddMissingDataTables( SelectQuery query, ReportColumnMapping groupByColumn, TableRelationshipGraphBuilder graphBuilder, List <string> tables, QueryBuilderBase queryBuilderBase, Dictionary <string, bool> addedTables) { // build a graph of the table relationships to calculate the distance from the root table // include missing tables needed for joins // order the tables by distance var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName); graphBuilder.TrimToTables(relationshipGraph, tables); tables = graphBuilder.GetAllTables(relationshipGraph).Union(tables).Distinct().ToList(); tables = tables.OrderBy(x => graphBuilder.GetDistance(relationshipGraph, x)).ToList(); foreach (var table in tables.Distinct()) { queryBuilderBase.AddFromTableJoin(query, groupByColumn, table, ref addedTables, graphBuilder, relationshipGraph); } }