public override void BuildFrom( SelectQuery query, List <ReportColumnMapping> selectedColumns, ReportColumnMapping groupByColumn, ReportColumnMapping sortByColumn, MappedSearchRequest request) { // idea here is to be able to get a key which can be joined on, so select from the table we're building the CTE from // then join on the tables required to get to the join key (which can be used as the group key) _rootTableName = request.GroupByColumn.KnownTable; var fromTable = _tableMappings.GetTableMapping(_fromTableName); var addedTables = new List <string>(); if (CanJoinTables(_fromTableName, _rootTableName)) { query.From(fromTable.DbTableName, fromTable.Alias); } else { var graphBuilder = new TableRelationshipGraphBuilder(); var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _rootTableName); // todo : use path //var path = graphBuilder.GetPathFromTableToRoot(relationshipGraph, _fromTableName); var distance = graphBuilder.GetDistance(relationshipGraph, _fromTableName); query.From(fromTable.DbTableName, fromTable.Alias); string currentTableName = fromTable.KnownTableName; while (distance > 1) { var potentialNodes = graphBuilder.GetByDistance(relationshipGraph, distance - 1); var parentNode = potentialNodes.SingleOrDefault(x => x.Relations.Any(y => y.TableName == currentTableName)); if (parentNode == null) { throw new Exception(string.Format("Could not find a relationship between {0} and {1}", currentTableName, relationshipGraph.TableName)); } JoinTables(query, parentNode.TableName, currentTableName); currentTableName = parentNode.TableName; distance--; addedTables.Add(parentNode.TableName); if (CanJoinTables(_fromTableName, currentTableName) && CanJoinTables(currentTableName, _rootTableName)) { break; } } } }
public virtual void AddFromTableJoin( SelectQuery query, ReportColumnMapping groupByColumn, string tableName, ref Dictionary <string, bool> addedTables, TableRelationshipGraphBuilder graphBuilder, TableRelationshipGraph relationshipGraph) { var joinTable = _tableMappings.GetTableMapping(tableName); if (joinTable.TableType == TableType.Stats) { return; } bool isAggregated = false; // tables with no child relationships if (!addedTables.ContainsKey(tableName)) { var distanceToRootTable = graphBuilder.GetDistance(relationshipGraph, tableName); var parentJoin = graphBuilder .GetByDistance(relationshipGraph, distanceToRootTable - 1) // the level above .SingleOrDefault(x => x.Relations.Any(y => y.TableName == tableName)); if (parentJoin != null) { var relation = parentJoin.Relations.First(x => x.TableName == tableName); if (relation.Type == TableRelationshipGraphType.Sibling) { //var parentTable = _tableMappings.GetTableMapping(parentJoin.TableName); var aggregatedTables = addedTables.Where(x => x.Value); var isSiblingOfAggregatedTable = aggregatedTables.Any(x => x.Value && TableIsSiblingOfTable(tableName, x.Key)); if (isSiblingOfAggregatedTable) { var parentTable = _tableMappings.GetTableMapping(groupByColumn.KnownTable); // if grouping by the parent of this table, then join on the primary key var joinForeignColumn = parentTable.PrimaryKey; query.LeftJoin(joinTable.CteAlias, joinTable.Alias, _constants.GroupKeyAlias, parentTable.Alias, joinForeignColumn); isAggregated = true; } else { JoinTables(query, tableName, parentJoin.TableName); } addedTables.Add(tableName, isAggregated); } else { var parentTable = _tableMappings.GetTableMapping(parentJoin.TableName); var isChild = TableIsChildOfTable(groupByColumn.KnownTable, joinTable.KnownTableName); var aggregatedViewRequired = !ColumnIsTableOrForeignKeyToTable(groupByColumn, joinTable) && !isChild; if (aggregatedViewRequired) { parentTable = _tableMappings.GetTableMapping(groupByColumn.KnownTable); // if the table being joined cant be connected to the group by table, the CTE will have been grouped by the root table id. if (!graphBuilder.TablesAreInSamePathToRoot(relationshipGraph, tableName, groupByColumn.KnownTable)) { parentTable = _tableMappings.GetTableMapping(_constants.RootTableName); } // if grouping by the parent of this table, then join on the primary key var joinForeignColumn = TableIsParentOfTable(groupByColumn.KnownTable, joinTable.KnownTableName) || parentTable.KnownTableName == _constants.RootTableName ? parentTable.PrimaryKey : _constants.GroupKeyAlias; if (addedTables.ContainsKey(parentTable.KnownTableName) && addedTables[parentTable.KnownTableName] == false) { joinForeignColumn = parentTable.PrimaryKey; } query.LeftJoin(joinTable.CteAlias, joinTable.Alias, _constants.GroupKeyAlias, parentTable.Alias, joinForeignColumn); isAggregated = true; } else { JoinTables(query, tableName, parentTable.KnownTableName); } addedTables.Add(tableName, isAggregated); } } else { throw new Exception(string.Format("Could not join table '{0}', no suitable relationship was found", tableName)); } } }
protected override void BuildGroupBy(SelectQuery query, ReportColumnMapping groupByColumn, MappedSearchRequest request) { _rootTableName = request.GroupByColumn.KnownTable; if (_rootTableName == _fromTableName) { var table = _tableMappings.GetTableMapping(_fromTableName); query.GroupBy(table.Alias, table.PrimaryKey); return; } var joinFromTable = _fromTableName; var graphBuilder = new TableRelationshipGraphBuilder(); if (!CanJoinTables(_fromTableName, _rootTableName)) { // if the table cannot be directly joined to the group by table, work out which join table to group on var fromTable = _tableMappings.GetTableMapping(_fromTableName); var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _rootTableName); var distance = graphBuilder.GetDistance(relationshipGraph, _fromTableName); joinFromTable = fromTable.KnownTableName; while (distance > 1) { var potentialNodes = graphBuilder.GetByDistance(relationshipGraph, distance - 1); var parentNode = potentialNodes.Single(x => x.Relations.Any(y => y.TableName == joinFromTable)); joinFromTable = parentNode.TableName; distance--; if (CanJoinTables(_fromTableName, joinFromTable) && CanJoinTables(joinFromTable, _rootTableName)) { break; } } } if (CanJoinTables(joinFromTable, _rootTableName)) { var relationship = GetTableRelationships(joinFromTable, _rootTableName).Single(); if (relationship.Table1.KnownTableName == joinFromTable) { query.GroupBy(relationship.Table1.Alias, relationship.Table1Column); } else if (relationship.Table2.KnownTableName == joinFromTable) { query.GroupBy(relationship.Table2.Alias, relationship.Table2Column); } else { throw new Exception("Failed to select group column"); } } else { throw new Exception(string.Format("Cannot join tables {0} to {1}", joinFromTable, _rootTableName)); } }