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));
}
}