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 TableInfo GetTableInfo()
{
var relations = _tableMappings.GetAllTableRelationships();
var graphBuilder = new TableRelationshipGraphBuilder();
var graph = graphBuilder.Build(relations, _constants.RootTableName);
var tables = _tableMappings.GetAllTables();
tables = tables.OrderBy(x => graphBuilder.GetDistance(graph, x.KnownTableName)).ToList();
return(new TableInfo
{
Tables = tables,
Relationships = relations
});
}
// Build
public override void BuildFrom(
SelectQuery query,
List <ReportColumnMapping> selectedColumns,
ReportColumnMapping groupByColumn,
ReportColumnMapping sortByColumn,
MappedSearchRequest request)
{
var statsRelationships = _tableMappings.GetAllTableRelationships()
.Where(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping)
.ToList();
// get the data table which joins onto the stats table
var graphBuilder = new TableRelationshipGraphBuilder();
var dataJoinTable = _dataSourceComponents.StatsQueryBuilder.GetDataJoinTable(request.GroupByColumn, statsRelationships, graphBuilder);
//-- Find out which stats table or view we need to query to satisfy the requested temporal
// aggregation and type of date range.
var statsTableName = GetStatsTableName(
_statsTableName,
dataJoinTable,
request.TemporalAggregation,
request.DateRangeType,
request.DateStart,
request.DateEnd);
query.From(statsTableName, _statsTableAlias);
JoinTables(query, dataJoinTable, _statsTableName);
// join up to the root table
var addedTables = new Dictionary <string, bool> {
{ dataJoinTable, false }
};
_dataSourceComponents.StatsQueryBuilder.AddDataTableJoinsUpToRoot(query, dataJoinTable, graphBuilder, addedTables);
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !addedTables.ContainsKey(currencyColumn.KnownTable))
{
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
AddFromTableJoin(query, groupByColumn, currencyColumn.KnownTable, ref addedTables, graphBuilder, relationshipGraph);
}
}
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 override ReportColumnMapping RestrictGroupBy(MappedSearchRequest request)
{
var statsRelationships = _tableMappings.GetAllTableRelationships()
.Where(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping)
.ToList();
// get the data table which joins onto the stats table
var graphBuilder = new TableRelationshipGraphBuilder();
var dataJoinTable = _dataSourceComponents.StatsQueryBuilder.GetDataJoinTable(request.GroupByColumn, statsRelationships, graphBuilder);
// get the stats relationship to the data join table
var statsRelationship = statsRelationships.FirstOrDefault(x => x.Table1.KnownTableName == dataJoinTable || x.Table2.KnownTableName == dataJoinTable);
var statForeignKeyToDataTable = statsRelationship.Table1.KnownTableName == dataJoinTable
? statsRelationship.Table2Column
: statsRelationship.Table1Column;
var groupByColumn = _columnProvider.Find(_constants.DataSourceId, _statsTableName, statForeignKeyToDataTable, null).First();
return(groupByColumn);
}
public override void BuildFrom(SelectQuery query, List <ReportColumnMapping> selectedColumns, ReportColumnMapping groupByColumn, ReportColumnMapping sortByColumn, MappedSearchRequest request)
{
var statsRelationships = _tableMappings.GetAllTableRelationships()
.Where(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping)
.ToList();
// get the data table which joins onto the stats table
var graphBuilder = new TableRelationshipGraphBuilder();
var dataJoinTable = _dataSourceComponents.StatsQueryBuilder.GetDataJoinTable(request.GroupByColumn, statsRelationships, graphBuilder);
//-- Find out which stats table or view we need to query to satisfy the requested temporal
// aggregation and type of date range.
var statsTableName = GetStatsTableName(
_transposeStatsTableName,
dataJoinTable,
request.TemporalAggregation,
request.DateRangeType,
request.DateStart,
request.DateEnd);
query.From(statsTableName, _transposeStatsTableAlias);
}
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);
}
}
public void AddDataTableJoinsUpToRoot(
SelectQuery query,
string dataTable,
TableRelationshipGraphBuilder graphBuilder,
Dictionary <string, bool> addedTables)
{
if (dataTable != _constants.RootTableName)
{
var fromTable = _tableMappings.GetTableMapping(dataTable);
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
var path = graphBuilder.GetPathFromTableToRoot(relationshipGraph, dataTable);
string currentTableName = fromTable.KnownTableName;
foreach (var table in path.Skip(1))
{
if (!addedTables.ContainsKey(table))
{
JoinTables(query, table, currentTableName);
addedTables.Add(table, false);
currentTableName = table;
}
}
}
}
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));
}
}
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 override Column GetColumnSelector(ReportColumnMapping col, MappedSearchRequest request, bool dontAggregate = false, bool useFieldAlias = false)
// {
// var result = base.GetColumnSelector(col, request, dontAggregate: dontAggregate, useFieldAlias: useFieldAlias);
// to allow selecting both min and max together (not working with custom columns - its complicated)
// depends on DefaultStatsQueryBuilder.GetColumnSelector()
// if (IsDateQuery(request) && (result.TableAlias??"").ToLower() == "stats" && !dontAggregate && result.Field.Name != _constants.CountKeyAlias)
// {
// result.Field.Name += "_" + col.FieldAggregationMethod;
// }
// return result;
// }
public override void BuildFrom(SelectQuery query, List <ReportColumnMapping> selectedColumns, ReportColumnMapping groupByColumn, ReportColumnMapping sortByColumn, MappedSearchRequest request)
{
using (new DebugTimer("StatsQueryBuilder.BuildFrom"))
{
var graphBuilder = new TableRelationshipGraphBuilder();
var tables = request.SelectedAndDependantColumns.Select(x => x.KnownTable).Distinct().ToList();
tables.Add(groupByColumn.KnownTable);
tables = tables.Distinct().ToList();
var statsRelationships = _tableMappings.GetAllTableRelationships()
.Where(x => x.Table1 is StatsTableMapping || x.Table2 is StatsTableMapping)
.ToList();
// get the data table which joins onto the stats table
var dataJoinTable = GetDataJoinTable(groupByColumn, statsRelationships, graphBuilder);
// get the stats relationship to the data join table
var statsRelationship = statsRelationships.FirstOrDefault(x => x.Table1.KnownTableName == dataJoinTable || x.Table2.KnownTableName == dataJoinTable);
string joinCol;
if (statsRelationship != null)
{
// var dataTable = statsRelationship.Table1.KnownTableName == groupByColumn.KnownTable//
var dataTable = statsRelationship.Table1.TableType == TableType.Data
? statsRelationship.Table1.KnownTableName
: statsRelationship.Table2.KnownTableName;
//var statForeignKeyToDataTable = statsRelationship.Table1.KnownTableName == groupByColumn.KnownTable
var statForeignKeyToDataTable = statsRelationship.Table1.TableType == TableType.Data
? statsRelationship.Table2Column
: statsRelationship.Table1Column;
query.From(GetStatsTableName(dataTable, request), _statsTableAlias);
JoinTables(query, dataTable, _statsTableName);
joinCol = statForeignKeyToDataTable;
var addedTables = new Dictionary <string, bool> {
{ dataTable, false }
};
// join back up to the root table todo : exclude tables not used for filtering or calculations
AddDataTableJoinsUpToRoot(query, dataTable, graphBuilder, addedTables);
// add data table joins : todo : exclude tables not used for filtering or calculations
AddMissingDataTables(query, groupByColumn, graphBuilder, tables, _dataSourceComponents.QueryBuilderBase, addedTables);
var currencyColumn = GetCurrencyColumn();
if (currencyColumn != null && !addedTables.ContainsKey(currencyColumn.KnownTable))
{
var relationshipGraph = graphBuilder.Build(_tableMappings.GetAllTableRelationships(), _constants.RootTableName);
AddFromTableJoin(query, groupByColumn, currencyColumn.KnownTable, ref addedTables, graphBuilder, relationshipGraph);
}
}
else
{
throw new Exception("No Relationship found to join the Stats table to " + groupByColumn.KnownTable);
}
var resolution = RestrictResolution(request);
var allTransposeStatsColumns = GetAllTransposeStatsColumns(request);
var distinctTransposeKeys = allTransposeStatsColumns.Select(x => QueryHelpers.GetTransposeKeyValue(x)).Distinct();
AddTransposeStatsJoins(query, resolution, distinctTransposeKeys, joinCol);
}
}
